SQL JOINS explained
02 November 2013 By Bhavyanshu Parasher
In this tutorial, i will try to explain the JOINS in MySQL with their example. I hope you would atleast have an idea of what happens in the joins. I am using MySQl as i am on linux and this is the most widely used open source RDBMS.
We have two tables table_A & table_B.
Inner join produces only the set of records that are matching in both Table A and Table B.
Full Outer Join
Full outer join produces the set of all records in Table A and Table B, with matching records from both sides. If there is no match, the missing side will contain NULL. Outer joins, on the other hand, are for finding records that may not have a match in the other table. As such, you have to specify which side of the join is allowed to have a missing record.
LEFT JOIN and RIGHT JOIN are shorthand for LEFT OUTER JOIN and RIGHT OUTER JOIN.
Below is the basic OUTER JOIN syntax. In MySQL we do not have full outer joins because of the above stated reason.
Left Outer Join
Left outer join produces a complete set of records from Table A, with the matching records (where available) in Table B. If there is no match, the right side will contain null.
To produce the set of records only in Table A, but not in Table B, we perform the same left outer join, then exclude the records we don’t want from the right side via a where clause.
There’s also a cartesian product or cross join, which as far as I can tell, can’t be expressed as a Venn diagram. This joins “everything to everything”, resulting in 4 x 3 = 12 rows, far more than we had in the original sets. If you do the math, you can see why this is a very dangerous join to run against large tables.
blog comments powered by Disqus