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

SQL JOIN

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.CROSS_JOINTo Perform this SQL join, CROSS JOIN Clause is used as below:

MySQL Syntax:

SQLite Syntax:

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.  INNER_JOINTo Perform this SQL join, INNER JOIN Clause is used as below:

MySQL Syntax:

SQLite Syntax:

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.LEFT_JOINTo Perform this SQL join, LEFT JOIN Clause is used as below:

MySQL Syntax:

SQLite Syntax:

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.SQL JOIN-RIGHT_JOINTo Perform this SQL join, RIGHT JOIN Clause is used as below:

MySQL Syntax:

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

Leave a Reply

avatar

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  Subscribe  
Notify of