Geek Answers Handbook

Sql joins - Joining multiple tables

I am trying to understand unification, and I'm a little confused. I know how to join tables using

= <=

IN exists and does not exist

I tried to try to understand the use of INNER JOIN, LEFT OUTER JOIN, USING etc., but this is so confusing. The main problem that I am facing is that different people refer to them using different names. Is there a simple explanation of the different types of joins and other names that they are known for. For example, while googling I came across the following types

 ♦ Simple Join ♦ Equi join ♦ Natural Join ♦ Outer Join ♦ Self Join ♦ Cartesian join ♦ Inner join ♦ Nonequi join ♦ Theta join ♦ Self join ♦ Cross join ♦ Cross Joins ♦ Natural Joins ♦ Inner Join with USING Clause ♦ Inner Join with ON Clause ♦ Left Outer Join ♦ Right Outer Join ♦ Full OuterJoin

Most of the above are duplicates, that is, its connection of the same type, but with a different name. I am sure that all of the above can be recreated using one of (= ,! =, but not in, in exists, etc.), but I'm struggling to understand what is the difference. The chart will probably help :)

+4
source share
5 answers

For some of them that were not explained in the mail to Matthew:

Simple Join - implicit join, defaults to internal join

 a join b on a.id = b.id

Natural Join is an inner join in all columns with the same name.

 a natural join b

Self Join is a join of a table to itself, it can be any other type of join (internal self-join, external self-join, etc.)

 a "a1" join a "a2" on "a1".id = "a2".id

A Cartesian join is every possible combination of rows; you will always get a product from the number of rows from both tables. You do this with an inner join without specifying a join condition

 a join b

Cross join is a synonym for cartesian union

Internal join with USING Clause is an alternative syntax for join conditions, you can use it if both tables have corresponding column names

 a join b using (id)

Inner Join with ON Clause - This is the same as I showed for a simple join, the only syntax is to join the where clause (as shown below)

 a join b where a.id = b.id

Left Outer Join - Same as Left Outer Join - same as right join. This looks like a left join, but you get zeros on the first table, not the second.

+2
source

By the way, just reading about JOIN. Has something changed in MySQL syntax? Oracle uses PLSQL syntax, which is slightly different from other databases.

I have the following situation here.

Fisrt Request

 SELECT * FROM tbl1 LEFT JOIN (tbl2, tbl3, tbl4) ON (tbl2.field1=tbl1.field1 AND tbl3.filed2=tbl1.field2 AND tbl4.field3=tbl1.field3)

Second request

 SELECT * FROM tbl1 LEFT JOIN (tbl2 CROSS JOIN tbl3 CROSS JOIN tbl4) ON (tbl2.field1=tbl1.field1 AND tbl3.filed2=tbl1.field2 AND tbl4.field3=tbl1.field3)

The Fisrt query is the same as Second Query .

Thanks everyone!

+2
source

EDIT: Just stumbled upon http://www.gplivna.eu/papers/sql_join_types.htm , which, although technical, provides a pretty good overview of sql join types. Worth a look

Here is another page with explained connections ( http://blog.noobtech.com/index.php/2009/02/sql-joins-visual-cheat-sheet/ )

The page I was about to publish was the one already posted by @matthew vines :)

+1
source

The Oracle SQL Language Reference contains a good section on which connections and different types of connections.

+1
source

More articles:


All Articles