
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_idThis 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_idThis 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_detailsThis 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_idNote:
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)
Hi Rakesh,
Can you let me know how to export the database using query.
I haven’t the phpmyadmin access and not SSH login
Hello Mahesh, I doubt such exists. If you do not have either of those access I think it is little difficult to export a database via SQL.
Love the tutorial. 😀
But you included LEFT OUTER JOIN twice and did not discuss FULL OUTER JOIN. 😦
Thanks for pointing it out Aleks. I have updated the post, hope it was helpful.
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.
Sorry Amit, MySQL does not support FULL JOIN, please try this option http://stackoverflow.com/a/4796911/765854 if you want.
Thanks…
ver helpful thanks
What the SQL Gods Join together, let no man tear apart…. Book of SQL. Chaper 6 Verse 19 Page 95
When we have same column names in two tables, which join to use in that time?
Join should be chosen based on the requirement and not based on the column.
If you have a problem in reading the column value from two different tables then I would suggest you to add an alias for the column and read the data. Please see: http://www.tutorialspoint.com/sql/sql-alias-syntax.htm
Thank you for the easy to understand explanation.
thank u very easy and understand. but please add the join queries question.
Hello Asha, not sure I understand your question. Can you point me to the question?