SQL Joins – Left, Right, Inner And Full Outer Join – SQL Tutorial
SQL Joins:
SQL joins are used to combine rows from two or more tables. Here we are going to discuss the following Joins using these you could return the data from two relational tables.
Must See: Cheat Sheet – Joins SQL
Types of SQL Joins:
i. INNER JOIN
ii. LEFT JOIN
iii. RIGHT JOIN
iv. OUTER JOIN
Must see: SQL Cheat Sheet Joins
INNER JOIN:
This query will return all of the records in the left table (table A) that have a matching record in the right table (table B).
SELECT column_name(s) FROM Table1 INNER JOIN Table2 ON Table1.Column_Name = Table2.Column_Name
SELECT * FROM SCOREBOARD INNER JOIN PLAYERS ON SCOREBOARD.Playername = PLAYERS.Playername
LEFT JOIN:
This query will return all of the records in the left table (table A) regardless if any of those records have a match in the right table (table B). It will also return any matching records from the right table.
SELECT column_name(s) FROM Table1 LEFT JOIN Table2 ON Table1.Column_Name = Table2.Column_Name
SELECT * FROM SCOREBOARD LEFT JOIN PLAYERS ON SCOREBOARD.Playername = PLAYERS.Playername
RIGHT JOIN:
This query will return all of the records in the right table (table B) regardless if any of those records have a match in the left table (table A). It will also return any matching records from the left table.
SELECT column_name(s) FROM Table1 RIGHT JOIN Table2 ON Table1.Column_Name = Table2.Column_Name
SELECT * FROM SCOREBOARD RIGHT JOIN PLAYERS ON SCOREBOARD.Playername = PLAYERS.Playername
FULL OUTER JOIN:
The FULL OUTER JOIN keyword returns all the rows from the left table (Customers), and all the rows from the right table (Orders). If there are rows in “Customers” that do not have matches in “Orders”, or if there are rows in “Orders” that do not have matches in “Customers”, those rows will be listed as well.
This Join can also be referred to as a FULL OUTER JOIN or a FULL JOIN. This query will return all of the records from both tables, joining records from the left table (table A) that match records from the right table (table B).
SELECT column_name(s) FROM Table1 FULL OUTER JOIN Table2 ON Table1.Column_Name = Table2.Column_Name
SELECT * FROM SCOREBOARD FULL JOIN PLAYERS ON SCOREBOARD.Playername = PLAYERS.Playername
Note on the OUTER JOIN that the inner joined records are returned first, followed by the right joined records, and then finally the left joined records (at least, that’s how my Microsoft SQL Server did it; this, of course, is without using any ORDER BY statement).
In the next tutorial, we will see AGGREGATE FUNCTIONS In SQL
Check out the complete SQL Tutorial by clicking on below link:
If you are not regular reader of my blog then I highly recommend you to signup for the free email newsletter using the below link.