SQL: Types of Join
SQL: Types of Join
In SQL, there are several types of joins that you can use to combine data from multiple tables based on specified conditions. Here are the most commonly used types of joins along with an explanation and a diagram:
- Inner Join:
- An inner join returns only the rows that have matching values in both tables being joined.
- It combines rows from two or more tables based on a related column between them.
- The resulting table contains only the matched records. Diagram:
+----------+ +----------+ +----------------+
| Table A | | Table B | | Inner Join |
+----------+ +----------+ +----------------+
| A_ID | | B_ID | | A_ID | B_ID |
+----------+ +----------+ +----------------+
| 1 | | 1 | | 1 | 1 |
| 2 | + | 3 | = | 2 | 3 |
| 3 | | 4 | | 3 | 4 |
+----------+ +----------+ +----------------+Example:
SELECT *
FROM TableA
INNER JOIN TableB ON TableA.A_ID = TableB.B_ID;- Left Join (or Left Outer Join):
- A left join returns all the rows from the left table and the matching rows from the right table.
- If there’s no match, it includes NULL values for the columns of the right table.
- The resulting table contains all records from the left table and the matching records from the right table. Diagram:
+----------+ +----------+ +----------------+
| Table A | | Table B | | Left Join |
+----------+ +----------+ +----------------+
| A_ID | | B_ID | | A_ID | B_ID |
+----------+ +----------+ +----------------+
| 1 | | 1 | | 1 | 1 |
| 2 | + | 3 | = | 2 | 3 |
| 3 | | 4 | | 3 | 4 |
| 4 | | 5 | | 4 | NULL |
+----------+ +----------+ +----------------+Example:
SELECT *
FROM TableA
LEFT JOIN TableB ON TableA.A_ID = TableB.B_ID;- Right Join (or Right Outer Join):
- A right join returns all the rows from the right table and the matching rows from the left table.
- If there’s no match, it includes NULL values for the columns of the left table.
- The resulting table contains all records from the right table and the matching records from the left table. Diagram:
+----------+ +----------+ +----------------+
| Table A | | Table B | | Right Join |
+----------+ +----------+ +----------------+
| A_ID | | B_ID | | A_ID | B_ID |
+----------+ +----------+ +----------------+
| 1 | | 1 | | 1 | 1 |
| 2 | + | 3 | = | 2 | 3 |
| 3 | | 4 | | 3 | 4 |
| NULL | | 5 | | NULL | 5 |
+----------+ +----------+ +----------------+Example:
SELECT *
FROM TableA
RIGHT JOIN TableB ON TableA.A_ID = TableB.B_ID;- Full Join (or Full Outer Join):
- A full join returns all the rows from both the left and right tables.
- If there’s no match, it includes NULL values for the columns of the non-matching table.
- The resulting table contains all records from both tables. Diagram:
+----------+ +----------+ +----------------+
| Table A | | Table B | | Full Join |
+----------+ +----------+ +----------------+
| A_ID | | B_ID | | A_ID | B_ID |
+----------+ +----------+ +----------------+
| 1 | | 1 | | 1 | 1 |
| 2 | + | 3 | = | 2 | 3 |
| 3 | | 4 | | 3 | 4 |
| 4 | | 5 | | 4 | NULL |
| NULL | | 6 | | NULL | 6 |
+----------+ +----------+ +----------------+Example:
SELECT *
FROM TableA
FULL JOIN TableB ON TableA.A_ID = TableB.B_ID;These diagrams represent the basic concepts of each join type. Keep in mind that in practical scenarios, you might have more columns in each table and additional join conditions to specify the relationships between them.
Technical content writer with data scientist, artificial intelligence, programming language, database. He has a bachelor’s degree in IT and a certificate in digital marketing, Digital transformation web development android app development He has written for website like Boomi techie, tech mantra, information hub, Tech all
