SQL JOIN

Quick Jump to:

SQL Join is one of the most frequently asked topics asked in interviews.

Now a days, database operations are very common while dealing with data storage across web/mobile apps.

Either to query a database stored in local(like SQLite) or a remote database (like MySQL), SQL operations help us retrieving the data easily.

SQL Join, in particular, helps to retrieve data from one or more tables in a Relational database based on common column/columns between them.

In this tutorial let’s discuss about various MySQL & SQLite Join statements.

Here is our YouTube video on SQL JOINS along with execution and examples from our channel CodeRefer. Do subscribe to receive all the latest video updates.

For ease of understanding , consider below sample database with Employees & Departments tables.

Employees Table:

Emp_id	Emp_name	Emp_Role	Emp_Hire_date	Dept_id
1	John	Admin	2000-02-02	1
2	Smith	Manager	2016-01-02	3
3	Abraham	Engineer	2018-01-02	2
4	Gracie	CEO	2000-01-02	1
5	Elias	Trainee	<null>	3
6	Marie	Operations	2018-07-20	4

Departments Table:

Dept_id	Dept_name	Location
1	HR	Newyork
2	Sales	Sunrise
3	Finance	Florida
4	Operations	<null>

Different types of Joins supported by MySQL Relational database are:

CROSS JOIN
INNER JOIN
LEFT JOIN
RIGHT JOIN

Note: MySQL does not support Full Outer Join.

Different types of Joins supported by SQLite Relational database are:

CROSS JOIN
INNER JOIN
LEFT JOIN

Note: SQLite does not support Right Outer Join & Full Outer Join.

CROSS JOIN:

Cross Join matches every row from first database table to every row of another database table.To Perform this SQL join, CROSS JOIN Clause is used as below:

MySQL Syntax:

	SELECT a.Emp_id, a.emp_name,b.Dept_name
	FROM employees a
	CROSS JOIN departments b;

view raw

mysql-cross-join.sql

hosted with ❤ by GitHub

SQLite Syntax:

	SELECT a.Emp_id, a.Emp_name, b.Department_name
	FROM Employees a
	CROSS JOIN Departments b;

view raw

sqlite-cross-join.sql

hosted with ❤ by GitHub

Resultant Output:

Emp_id	Emp_name	Dept_name
1	John	HR
1	John	Sales
1	John	Finance
1	John	Operations
2	Smith	HR
2	Smith	Sales
2	Smith	Finance
2	Smith	Operations
3	Abraham	HR
3	Abraham	Sales
3	Abraham	Finance
3	Abraham	Operations
4	Gracie	HR
4	Gracie	Sales
4	Gracie	Finance
4	Gracie	Operations
5	Elias	HR
5	Elias	Sales
5	Elias	Finance
5	Elias	Operations
6	Marie	HR
6	Marie	Sales
6	Marie	Finance
6	Marie	Operations

Note: WHERE Clause is not specified in CROSS JOIN. By Specifying WHERE Clause, this kind of SQL Join will function as INNER JOIN.

INNER JOIN:

Inner Join produces rows from two joined tables with common values which satisfy the given condition with non-NULL values. To Perform this SQL join, INNER JOIN Clause is used as below:

MySQL Syntax:

	SELECT a.Emp_id, a.Emp_name,b.Dept_name
	FROM employees a
	INNER JOIN departments b
	ON a.Dept_id=b.Dept_id;

view raw

mysql-inner-join.sql

hosted with ❤ by GitHub

SQLite Syntax:

	SELECT a.emp_id, a.emp_name, b.Department_name
	FROM employees a
	INNER JOIN Departments b
	ON a.Dept_id = b.Dept_id;

view raw

sqlite-inner-join.sql

hosted with ❤ by GitHub

Resultant Output:

Emp_id	Emp_name	Dept_name
1	John	HR
2	Smith	Finance
3	Abraham	Sales
4	Gracie	HR
5	Elias	Finance
5	Marie	Operations

LEFT JOIN:

As Inner Join, Left Join also requires a condition need to be satisfied. Left Join returns all the rows of Left table even if there are no matching rows found in the right table. NULL is returned when no matches found in the right table.To Perform this SQL join, LEFT JOIN Clause is used as below:

MySQL Syntax:

	SELECT a.Emp_id, a.Emp_name,a.Emp_Hire_date,b.Dept_name
	FROM employees a
	LEFT JOIN departments b
	ON a.Dept_id=b.Dept_id
	ORDER BY a.Emp_id;

view raw

mysql-left-join.sql

hosted with ❤ by GitHub

SQLite Syntax:

	SELECT a.Emp_id, a.Emp_name,a.Emp_Hire_date,b.Dept_name
	FROM Employees a
	LEFT OUTER JOIN Departments b
	ON a.Dept_id = b.Dept_id;

view raw

sqlite-left-join.sql

hosted with ❤ by GitHub

Resultant Output:

Emp_id	Emp_name	Emp_Hire_date	Dept_name
1	John	2000-02-02	HR
2	Smith	2016-01-02	Finance
3	Abraham	2018-01-02	Sales
4	Gracie	2000-01-02	HR
5	Elias	<null>	Finance
6	Marie	2018-07-20	Operations

RIGHT JOIN:

RIGHT JOIN is exactly the opposite of LEFT JOIN. Right Join returns all the rows of Right table even if there are no matching rows found in the left table. NULL is returned when no matches found in the left table.To Perform this SQL join, RIGHT JOIN Clause is used as below:

MySQL Syntax:

	SELECT a.Emp_id, a.Emp_name,a.Emp_Role,b.Dept_id,b.Dept_name,b.Location
	FROM employees a
	RIGHT JOIN departments b
	on a.Dept_id = b.Dept_id
	ORDER BY a.Emp_id;

view raw

mysql-right-join.sql

hosted with ❤ by GitHub

SQLite Syntax:

SQLite does not support Right Outer Join.

Resultant Output:

Emp_id	Emp_name	Emp_Role	Dept_id	Dept_name	Location
1	John	Admin	1	HR	Newyork
2	Smith	Manager	3	Finance	Florida
3	Abraham	Engineer	2	Sales	Sunrise
4	Gracie	CEO	1	HR	Newyork
5	Elias	Trainee	3	Finance	Florida
6	Marie	Operations	4	Operations

SQL JOIN – CROSS, INNER, LEFT, RIGHT joins with Examples