A SQL join clause combines records from two or more tables in a relational database. It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining fields from two tables (or more) by using values common to each.
Let's look at the different kinds of SQL joins:
Inner Join
An inner join produces a result set that is limited to the rows where there is a match in both tables for what we're looking for. If you don't know which kind of join you need, this will usually be your best bet.
Example:
Example:
Example:
Example
Example:
Example:
Different SQL JOINs
- INNER JOIN: Returns all rows when there is at least one match in BOTH tables
- LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
- RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
- FULL JOIN: Return all rows when there is a match in ONE of the tables
Types of SQL Joins
As we said above, a SQL join is a instruction to a database to combine data from more than one table. There are different kinds of joins, which have different rules for the results they create.Let's look at the different kinds of SQL joins:
Inner Join
An inner join produces a result set that is limited to the rows where there is a match in both tables for what we're looking for. If you don't know which kind of join you need, this will usually be your best bet.
Example:
SELECT gid, first_name, last_name, pid, gardener_id, plant_name
FROM Gardners
INNER JOIN Plantings
ON gid = gardener_id
Left Outer Join
A left outer join, or left join, results in a set where all of the rows from the first, or left hand side, table are preserved. The rows from the second, or right hand side table only show up if they have a match with the rows from the first table. Where there are values from the left table but not from the right, the table will read null, which means that the value has not been set.Example:
SELECT gid, first_name, last_name, pid, gardener_id, plant_name
FROM Gardners
LEFT OUTER JOIN Plantings
ON gid = gardener_id
Right Outer Join
A right outer join, or right join, is the same as a left join, except the roles are reversed. All of the rows from the right hand side table show up in the result, but the rows from the table on the left are only there if they match the table on the right. Empty spaces are null, just like with the the left join.Example:
SELECT gid, first_name, last_name, pid, gardener_id, plant_name
FROM Gardners
RIGHT OUTER JOIN Plantings
ON gid = gardener_id
Full Outer Join
A full outer join, or just outer join, produces a result set with all of the rows of both tables, regardless of whether there are any matches. Similarly to the left and right joins, we call the empty spaces null.Example
SELECT gid, first_name, last_name, pid, gardener_id, plant_name
FROM Gardners
FULL OUTER JOIN Plantings
ON gid = gardener_id
Cross Join
The cross join returns a table with a potentially very large number of rows. The row count of the result is equal to the number of rows in the first table times the number of rows in the second table. Each row is a combination of the rows of the first and second table.Example:
SELECT gid, first_name, last_name, pid, gardener_id, plant_name
FROM Gardners
CROSS JOIN Plantings
Self Join
You can join a single table to itself. In this case, you are using the same table twice.Example:
SELECT G1.gid, G1.first_name, G1.last_name, G2.gid, G2.first_name, G2.last_name
FROM Gardners G1
INNER JOIN Gardners G2
ON G1.first_name = G2.first_name
