Introduction
JOIN is used to fetch data from two or more tables. It is used for combining column from two or more tables by using values common to both tables. JOIN allow you to combine data from two tables.
Join Type
Following are the types of JOIN:
- JOIN: Select all records from left table and right table, where the join condition is met.
- LEFT JOIN: Select all the rows from the left table and the matching rows from the right table. If no matching rows found in the right table, NULL are used.
- RIGHT JOIN: Select all the rows from the right table and the matching rows from the left table. If a row in the right table does not have any matching rows from the left table, the column of the left table in the result set to NULL.
- FULL JOIN: It returns a result set that includes rows from both left and right tables. When no matching rows exist for the row in the left table, the columns of the right table will have NULL. Similarly, when no matching rows exist for the row in the right table, the column of the left table will have NULL.
Example
Create two tables named candidates and employee and insert data into these tables.
/* Create Table candidates*/
CREATE TABLE candidates(
id INT PRIMARY KEY AUTO_INCREMENT,
fullname VARCHAR(100) NOT NULL
);
/* Insert record in the table */
INSERT INTO
candidates(fullname)
VALUES
('John Smith'),
('Lily Bush'),
('Peter Drucker'),
('Om Singh');
/* Create Table employees*/
CREATE TABLE employees(
id INT PRIMARY KEY AUTO_INCREMENT,
fullname VARCHAR(100) NOT NULL
);
/* Insert record in the table */
INSERT INTO
employees(fullname)
VALUES
('John Smith'),
('Om Singh'),
('Michael Scott'),
('Jack Sparrow');Inner Join
Inner join produces a data set that includes rows from the left table which have matching rows from the right table.
SELECT candidates.id AS C_ID, candidates.fullname AS C_FullName, employees.id AS E_ID, employees.fullname as E_FullName FROM candidates INNER JOIN employees ON employees.fullname = candidates.fullname /* Query Result */ C_ID C_FullName E_ID E_FullName 1 John Smith 1 John Smith 4 Om Singh 2 Om Singh
Left Join
Left Join (or Left Outer Join) returns all rows from the left table and the matching rows from the right table. If a row in the left table does not have a matching row in the right table, the columns of the right table will have nulls. The OUTER keyword is optional.
SELECT candidates.id AS C_ID, candidates.fullname AS C_FullName, employees.id AS E_ID, employees.fullname as E_FullName FROM candidates LEFT JOIN employees ON employees.fullname = candidates.fullname ORDER BY C_ID /* Query Result */ C_ID C_FullName E_ID E_FullName 1 John Smith 1 John Smith 2 Lily Bush NULL NULL 3 Peter Drucker NULL NULL 4 Om Singh 2 Om Singh
Right Join
Right Join (or Right Outer Join) returns a result set that contains all rows from the right table and the matching rows in the left table. If a row in the right table that does not have a matching row in the left table, all columns in the left table will contain nulls. The OUTER keyword is optional. It is a reversed version of the left join.
SELECT candidates.id AS C_ID, candidates.fullname AS C_FullName, employees.id AS E_ID, employees.fullname as E_FullName FROM candidates RIGHT JOIN employees ON employees.fullname = candidates.fullname ORDER BY E_ID /* Query Result */ C_ID C_FullName E_ID E_FullName 1 John Smith 1 John Smith 4 Om Singh 2 Om Singh NULL NULL 3 Michael Scott NULL NULL 4 Jack Sparrow
Full Join
Full Join (or Full Outer Join) returns a result set that contains all rows from both left and right tables, with the matching rows from both sides where available. In case there is no match, the missing side will have NULL values. The OUTER keyword is optional.
SELECT candidates.id AS C_ID, candidates.fullname AS C_FullName, employees.id AS E_ID, employees.fullname as E_FullName FROM candidates FULL JOIN employees ON employees.fullname = candidates.fullname ORDER BY E_ID