SQL Join is to combine data from two or more tables based on a common field among them. Joins indicate how SQL should use data from one table to select the rows in another table. By using joins, you can retrieve data from two or more tables based on logical relationships between the tables in a database. In this blog post, we will give an introduction to SQL Joins.
SELECT a.Column1, b.Column2 FROM TableA a INNER JOIN TableB b ON a.Id = b.AFKId
Types of Join
Below are the most common joins.
- Inner Join
- Left Join or Left Outer Join
- Right Join or Right Outer Join
- Full Join
INNER JOIN
This join selects the matching data from the tables based on a common key. It will create the result set by combining all rows from the tables where the value of the common field will be the same.
// Joining two tables on common emp_no
SELECT * FROM employees e JOIN salaries s ON (e.emp_no = s.emp_no) LIMIT 5;
+--------+------------+------------+-----------+--------+------------+--------+--------+------------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date | emp_no | salary | from_date | to_date |
+--------+------------+------------+-----------+--------+------------+--------+--------+------------+------------+
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 | 10001 | 60117 | 1986-06-26 | 1987-06-26 |
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 | 10001 | 62102 | 1987-06-26 | 1988-06-25 |
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 | 10001 | 66074 | 1988-06-25 | 1989-06-25 |
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 | 10001 | 66596 | 1989-06-25 | 1990-06-25 |
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 | 10001 | 66961 | 1990-06-25 | 1991-06-25 |
+--------+------------+------------+-----------+--------+------------+--------+--------+------------+------------+
LEFT JOIN or LEFT OUTER JOIN
This join returns all the records of the table on the left side of the join and matching rows for the table on the right side of the join. If there is no matching row on the right-hand side of the join, the result set will contain null.
SELECT * FROM employees e LEFT OUTER JOIN salaries s ON (e.emp_no = s.emp_no) LIMIT 5;
RIGHT JOIN or RIGHT OUTER JOIN
This join returns all the records of the table on the right side of the join and matching rows for the table on the left side of join.
If there is no matching row on the left side of the join, the result set will contain null values.
FULL JOIN
This join creates the result set by combining the result of both the LEFT and RIGHT Joins. The result set will contain all the rows from both tables. If there is no match found for any rows, the result set will contain NULL values.
Figure: Join