What are the different join operations in sql?

Question

What are the benefits of using various join operations in sql? How do I want to know why we need different internal and external connections?

Answer 1

The only type of connection you really need is LEFT OUTER JOIN . Each other type of join can be rewritten in terms of one or more left outer joins and possibly some filtering. So why do we all the rest? Is it just confusing people? Wouldn't it be easier if there was only one type of connection?

You may also ask: why is there a <= b and b >= a ? Don't they do the same? Can't we get rid of one of them? This will simplify the situation!

It is sometimes easier to exchange <= on >= instead of replacing the round arguments. In the same way, the left join and the right join are the same thing, only with the replacement of operands. But then again, it’s practical to have both options instead of requiring people to write their requests in a specific order.

One more thing you could ask: logical, why do we have AND , OR , NOT , XOR , NAND , NOR , etc.? All this can be rewritten in terms of NAND s! Why not just NAND ? Well, it’s inconvenient to write OR in terms of NAND s, and it’s not so obvious that the intention is that if you write OR , people immediately understand what you mean. If you are writing a bunch of NAND s, it is not clear what you are trying to achieve.

Similarly, if you want to make a FULL OUTER JOIN b , you can make a left join and a right join, remove duplicate results, and then merge everything. But this is pain, and therefore there is a contraction for her.

When do you use each of them? Here's a simplified rule:

• If you always want a row of results for each row in the LEFT table, use the LEFT OUTER JOIN.
• If you always want to get a row of results for each row in the RIGHT table, use the CORRECT EXTERNAL JOINT.
• If you always need a row of results for each row in any table, use FULL OUTER JOIN.
• If you only need a row of results when there is a row in both tables, use INNER JOIN.
• If you need all possible pairs of rows, one row from each table, use CROSS JOIN.

Answer 2

This link should clear all JOIN-related doubts:

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

Answer 3

inner join - combines rows from both sets of matching based on the specified criteria.

outer join - selects all one set together with corresponding or empty (if not comparable) elements from another set. Outer joins can be left or right to indicate which set is returned fully.

Answer 4

To make the other answers clearer, YOU GET DIFFERENT RESULTS according to the join you choose, when the columns you are joining contain zero values ​​- for example.

So - for each scenario in real life there is a connection suitable for it (either you want rows without data, or not in the example with zero values).

Answer 5

My answer assumes that the two tables are combined on the same key:

• INNER JOIN - get the results that are in both join tables (in accordance with the join rule)
• FULL OUTER JOIN - get all the results from both tables (Cartesian product)
• LEFT OUTER JOIN - get all the results from the left table and the corresponding results to the right

You can add WHERE clauses to further limit the results.

Use them to get only what you want to receive.