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 sql–joins in a simple/neat explanation.

Today I am going to talk about SQL Joins yes, there are quite a bit of types: Join, Inner Join, Cross 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:
  • A 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:

• MySQL Unsigned INT to Signed INT – A Valid Performance For Index (rakeshsankar.wordpress.com)