(My)SQL Joins – JOIN, CROSS, INNER, OUTER (LEFT, RIGHT, FULL)


The official logo of the database query MySQL
Image via Wikipedia

It’s been quite some-time since I wrote a blog – sorry guys didn’t get any time to write. But today I got a good one, a place where you find info about sqljoins in a simple/neat explanation.

Today I am going to talk about SQL Joins yes, there are quite a bit of types: Join, Inner JoinCross Join, Outer Join (OUTER Join further divides into Left Join, Right Join and Full Join).

JOIN:

  • A JOIN command joins/combines rows from more than 1 table.
  • Two tables that want to join together need to have a common field, like in the following example, employee_id is common.

Syntax:

SELECT [COL-1], [COL-2] [, … COL-N]
FROM [TABLE-1] , [TABLE-2]
WHERE [TABLE-1].[COLUMN NAME 1] = [TABLE 2].[COLUMN NAME 2]

Example:

SELECT emp.employee_id, emp.employee_name, emd.employee_salary
FROM employee emp, employee_details emd
WHERE emp.employee_id = emd.employee_id

This will return a rows of data only if employee_id from employee table matches with employee_id from employee_details.

INNER JOIN:

  • INNER JOIN or JOIN does the same job.
  • A INNER JOIN command queries joins/combines rows from more than 1 table.
  • Two tables that want to join together need to have a common field, like in the following example, employee_id is common.

Syntax:

SELECT [COL-1], [COL-2] [, … COL-N]
FROM [TABLE-1] , [TABLE-2]
INNER JOIN [TABLE-1].[COLUMN NAME 1] = [TABLE 2].[COLUMN NAME 2]

Example:

SELECT emp.employee_id, emp.employee_name, emd.employee_salary
FROM employee emp, employee_details emd
INNER JOIN emp.employee_id = emd.employee_id

This will return a rows of data only if employee_id from employee table matches with employee_id from employee_details.

CROSS JOIN:

  • CROSS JOIN is a query that returns all the records where each row from the table-1 is matched with each row from the table-2.

Syntax:

SELECT [COL-1], [COL-2] [, … COL-N]
FROM [TABLE-1] , [TABLE-2]

(OR)

SELECT [COL-1], [COL-2] [, … COL-N] 
FROM
[TABLE-1]
CROSS JOIN
[TABLE-1]

Example:

SELECT *
FROM employee
CROSS JOIN employee_details

This will return all the rows from both employee and employee_details table.

OUTER JOIN:

OUTER JOIN divides into LEFT OUTER JOIN, RIGHT OUTER JOIN and FULL OUTER JOIN.

  • LEFT OUTER JOIN:
    • A LEFT OUTER JOIN returns all the records from the left table irrespective of the match with the right table.

Syntax:

SELECT [COL-1], [COL-2] [, … COL-N]
FROM [TABLE-1]
LEFT OUTER JOIN [TABLE-2] ON [TABLE-1].[COLUMN NAME 1] = [TABLE 2].[COLUMN NAME 2]

Example:

SELECT *
FROM employee_details
LEFT OUTER JOIN employee ON employee_details.employee_id = employee.employee_id

  • RIGHT OUTER JOIN:
    • RIGHT OUTER JOIN returns all the records from the right table irrespective of the match with the left table.

Syntax:

SELECT [COL-1], [COL-2] [, … COL-N]
FROM [TABLE-1]
RIGHT OUTER JOIN [TABLE-2] ON [TABLE-1].[COLUMN NAME 1] = [TABLE 2].[COLUMN NAME 2]

Example:

SELECT *
FROM employee
RIGHT OUTER JOIN employee_details ON employee_details.employee_id = employee.employee_id

  • FULL OUTER JOIN:
    • A FULL OUTER JOIN combines the results of both left and right outer joins and returns all (matched or unmatched) rows from the tables on both sides of the table.

Syntax:

SELECT [COL-1], [COL-2] [, … COL-N]
FROM [TABLE-1]
FULL OUTER JOIN [TABLE-2] ON [TABLE-1].[COLUMN NAME 1] = [TABLE 2].[COLUMN NAME 2]

Example:

SELECT *
FROM employee_details
FULL OUTER JOIN employee ON employee_details.employee_id = employee.employee_id

Note: 

FULL JOINs does not work on MySQL but you can emulate it, please visit: http://stackoverflow.com/a/4796911/765854 for more information.

Happy Learning!

Related Articles:

(My)SQL Joins – JOIN, CROSS, INNER, OUTER (LEFT, RIGHT, FULL)

14 thoughts on “(My)SQL Joins – JOIN, CROSS, INNER, OUTER (LEFT, RIGHT, FULL)

  1. Mahesh says:

    Hi Rakesh,

    Can you let me know how to export the database using query.
    I haven’t the phpmyadmin access and not SSH login

  2. Aleks Stypulkowska says:

    Love the tutorial.😀
    But you included LEFT OUTER JOIN twice and did not discuss FULL OUTER JOIN.😦

  3. Amit Kumar says:

    Hi, the Full Outer Join is not working, giving some error – ” Error 1064 (42000): you have an error on your sql syntax; check the manual that corresponds to your mysql server version for that right syntax to use near ‘outer join department on employee.DepID=department.DepID’ at line 1. ”
    I am using mysql server 5.1 dosprompt.(Not windows based).
    all query are working i.e. left outer join, right outer join except full outer join.
    the query is:
    select * from employee full outer join department on employee.DepID=department.DepID;

    just change full to right or left it works. So, is there any other syntax for full outer join?
    Plz tell me if u have any idea abt that.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s