A JOIN clause is used to combine rows from two or more tables, based on a related Column between them
Different Types of SQL JOINs
Here are the different types of the JOINs in SQL:
- (INNER) JOIN: Returns records that have matching values in both tables
- LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
- RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
- FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table
Employee
| EMP_ID | EMP_NAME | CITY | SALARY | AGE |
| 1 | Angelina | Chicago | 200000 | 30 |
| 2 | Robert | Austin | 300000 | 26 |
| 3 | Christian | Denver | 100000 | 42 |
| 4 | Kristen | Washington | 500000 | 29 |
| 5 | Russell | Los angels | 200000 | 36 |
| 6 | Marry | Canada | 600000 | 48 |
Project
| PROJECT_NO | EMP_ID | DEPARTMENT |
| 101 | 1 | Testing |
| 102 | 2 | Development |
| 103 | 3 | Designing |
| 104 | 4 | Development |
INNER JOIN
The INNER JOIN keyword selects records that have matching values in both tables.
INNER JOIN Syntax
SELECT column_name(s)
FROM table1
INNER JOIN table2ON table1.column_name = table2.column_name;
Example
SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT
FROM EMPLOYEE
INNER JOIN PROJECT
ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;
OUTPUT
| EMP_NAME | DEPARTMENT |
| Angelina | Testing |
| Robert | Development |
| Christian | Designing |
| Kristen | Development |
Left JOIN
The SQL left join returns all the values from left table and the matching values from the right table. If there is no matching join value, it will return NULL.
LEFT JOIN Syntax
SELECT column_name(s)
FROM table1
LEFT JOIN table2ON table1.column_name = table2.column_name;
Example
SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT
FROM EMPLOYEE
LEFT JOIN PROJECT
ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;
OUTPUT
| EMP_NAME | DEPARTMENT |
| Angelina | Testing |
| Robert | Development |
| Christian | Designing |
| Kristen | Development |
| Russell | NULL |
| Marry | NULL |
Right JOIN
The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records from the left table (table1). The result is 0 records from the left side, if there is no match.
Right JOIN Syntax
SELECT column_name(s)
FROM table1
RIGHT JOIN table2ON table1.column_name = table2.column_name;
Example
SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT
FROM EMPLOYEE
RIGHT JOIN PROJECT
ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;
OUTPUT
| EMP_NAME | DEPARTMENT |
| Angelina | Testing |
| Robert | Development |
| Christian | Designing |
| Kristen | Development |
Full JOIN
In SQL, FULL JOIN is the result of a combination of both left and right outer join. Join tables have all the records from both tables. It puts NULL on the place of matches not found.
Full JOIN Syntax
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2ON table1.column_name = table2.column_nameWHERE condition;
Example
SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT
FROM EMPLOYEE
FULL JOIN PROJECT
ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;
Output
| EMP_NAME | DEPARTMENT |
| Angelina | Testing |
| Robert | Development |
| Christian | Designing |
| Kristen | Development |
| Russell | NULL |
| Marry | NULL |
Advantages and Disadvantages of Joins
The main advantage of a join is that it executes faster. The performance increase might not be noticeable by the end user. However, because the columns are specifically named and indexed and optimized by the database engine, the retrieval time almost always will be faster than that of a subquery. There are also inner and outer joins, left and right joins, full joins and cross joins. A disadvantage of using joins is that they are not as easy to read as subqueries. Another disadvantage is that it can be confusing as to which join is the appropriate type of join to use to yield the correct desired result set.