What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?
Solution 1
Reading this original article on The Code Project will help you a lot: Visual Representation of SQL Joins.
Also check this post: SQL SERVER – Better Performance – LEFT JOIN or NOT IN?.
Find original one at: Difference between JOIN and OUTER JOIN in MySQL.
Solution 2
An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.
There are different types of joins available in SQL:
INNER JOIN: returns rows when there is a match in both tables.
LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table.
RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table.
FULL JOIN: combines the results of both left and right outer joins.
The joined table will contain all records from both the tables and fill in NULLs for missing matches on either side.
SELF JOIN: joins a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
CARTESIAN JOIN: returns the Cartesian product of the sets of records from the two or more joined tables.
We can take each first four joins in Details :
We have two tables with the following values.
TableA
id firstName lastName
.......................................
1 arun prasanth
2 ann antony
3 sruthy abc
6 new abc
TableB
id2 age Place
................
1 24 kerala
2 24 usa
3 25 ekm
5 24 chennai
....................................................................
INNER JOIN
Note : gives the intersection of the two tables, i.e. rows TableA and TableB have in common.
Syntax
SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;
Apply it in our sample table :
SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
FROM TableA
INNER JOIN TableB
ON TableA.id = TableB.id2;
Result
firstName lastName age Place
..............................................
arun prasanth 24 kerala
ann antony 24 usa
sruthy abc 25 ekm
LEFT JOIN
Note : gives all selected rows in TableA, plus any common selected rows in TableB.
Syntax
SELECT table1.column1, table2.column2...
FROM table1
LEFT JOIN table2
ON table1.common_field = table2.common_field;
Apply it in our sample table :
SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
FROM TableA
LEFT JOIN TableB
ON TableA.id = TableB.id2;
Result
firstName lastName age Place
...............................................................................
arun prasanth 24 kerala
ann antony 24 usa
sruthy abc 25 ekm
new abc NULL NULL
RIGHT JOIN
Note : gives all selected rows in TableB, plus any common selected rows in TableA.
Syntax
SELECT table1.column1, table2.column2...
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field;
Apply it in our sample table :
SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
FROM TableA
RIGHT JOIN TableB
ON TableA.id = TableB.id2;
Result
firstName lastName age Place
...............................................................................
arun prasanth 24 kerala
ann antony 24 usa
sruthy abc 25 ekm
NULL NULL 24 chennai
FULL JOIN
Note : returns all selected values from both tables.
Syntax
SELECT table1.column1, table2.column2...
FROM table1
FULL JOIN table2
ON table1.common_field = table2.common_field;
Apply it in our sample table :
SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
FROM TableA
FULL JOIN TableB
ON TableA.id = TableB.id2;
Result
firstName lastName age Place
...............................................................................
arun prasanth 24 kerala
ann antony 24 usa
sruthy abc 25 ekm
new abc NULL NULL
NULL NULL 24 chennai
Interesting Fact
- For INNER joins the order doesn't matter.
- For (LEFT, RIGHT or FULL) OUTER joins, the order matters.
Better to go check this Link it will give you interesting details about join order.
Solution 3
INNER JOIN gets all records that are common between both tables based on the supplied ON clause.
LEFT JOIN gets all records from the LEFT linked and the related record from the right table ,but if you have selected some columns from the RIGHT table, if there is no related records, these columns will contain NULL.
RIGHT JOIN is like the above but gets all records in the RIGHT table.
FULL JOIN gets all records from both tables and puts NULL in the columns where related records do not exist in the opposite table.
Lion King
I am just a human, was born like any person, will live like the others, and when my time comes I will die.
Updated on August 27, 2021Comments
-
Lion King over 2 years
What's the difference between
INNER JOIN
,LEFT JOIN
,RIGHT JOIN
andFULL JOIN
in MySQL?-
Brad about 13 years
-
Cups about 13 yearsThe explanation by Jeff Attwood featuring Venn diagrams might do it for you. A Visual Explanation of SQL Joins
-
aleroot almost 13 yearsFULL outer JOIN doesn't exist on MySQL
-
Lightness Races in Orbit almost 13 yearsI note that none the answers are specific to MySQL, which does have its own semantics in some areas. I would also like to see a decent write-up.
-
philipxy over 4 years@Cups Atwood repudiates that very post in its comments. Venn diagrams are mostly abused on this topic & cannot explain fully. See my comments on the (terrible) answer by PranayRana & at the duplicate link.
-
-
onedaywhen over 12 yearsThese pictures don't do it for me. Why is the top right picture not simply
SELECT * FROM TableA;
? Why is the top left picture not simplySELECT * FROM TableB;
? Why is the top middle picture notSELECT * FROM A INTERSECT SELECT * FROM B
? etc -
onedaywhen over 12 yearsI have a problem with the whole concept: those are visual representations of union, intersect, except, etc. They have no visual representation of projection therefore cannot be joins. I think it will confuse more than benefit when the context is joins.
-
Induster almost 12 yearsHave to disagree. I think these are good visualizations. At a glance you can see what will be selected from the two tables when using a certain join. "onedaywhen" commented "why not just say select * from table a".... well because it's a join and needs two tables lol.
-
bjedrzejewski over 10 yearsSQL is a language that reads left to right- yes? :)
-
ubanerjea over 9 yearsThis diagram has one major problem, which is that it completely ignores the difference between semi-joins and joins. That is:
select a.* from a inner join b on a.id = b.id
vs.select a.* from a where id in (select id from b)
. That is because SQL joins are NOT the intersection of two sets- the join can be one->one, one->many, or many->many. So it's actually impossible to represent with a Venn diagram: all this diagram does is show you "what portion of the table will be involved in the join." In which caseselect a.* from a cross join b
should have the same diagram as full outer join. -
nietaki almost 9 yearsThat's technically not correct: "INNER JOIN gets all records from one table that have some related entry in a second table" - INNER JOIN doesn't just return records from one table.
-
John Smith over 7 years
full join
is not the same thing as aunion
. See stackoverflow.com/questions/905379/… and social.msdn.microsoft.com/Forums/sqlserver/en-US/… -
Sergio A. over 2 yearsI always come back to this answer every-time I'm in doubt about different joins practical applications. Thanks from the past mate
-
DotNet Team about 2 yearsHelped me to understand better difference with image..!