1. Inner Join :
It is a type of join operation in SQL. Inner join is an operation that returns a combined tuples between two or more tables where at least one attribute in common. If there is no attribute in common between tables then it will return nothing.
select * from table1 INNER JOIN table2 on table1.column_name = table2.column_name;
select * from table1 JOIN table2 on table1.column_name = table2.column_name;
2. Outer Join :
It is a type of Join operation in SQL. Outer join is an operation that returns a combined tuples from a specified table even the join condition will fail. There are three types of outer join in SQL i.e.
- Left Outer Join
- Right Outer Join
- Full Outer Join
Syntax of Left Outer Join:
select * from table1 LEFT OUTER JOIN table2 on table1.column_name = table2.column_name;
Syntax of Right Outer Join:
select * from table1 RIGHT OUTER JOIN table2 on table1.column_name = table2.column_name;
Syntax of Full Outer Join:
select * from table1 FULL OUTER JOIN table2 on table1.column_name = table2.column_name;
Below is a table of difference between INNER JOIN and OUTER JOIN:
|S.No||Inner Join||Outer Join|
|1.||It returns the combined tuple between two or more tables.||It returns the combined tuple from a specified table even join condition will fail.|
|2.||Used clause INNER JOIN and JOIN.||Used clause LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, etc.|
|3.||When any attributes are not common then it will return nothing.||It does not depend upon the common attributes. If the attribute is blank then here already placed NULL.|
|4.||If tuples are more. Then INNER JOIN works faster than OUTER JOIN.||Generally, The OUTER JOIN is slower than INNER JOIN. But except for some special cases.|
|5.||It is used when we want detailed information about any specific attribute.||It is used when we want to complete information.|
|6.||JOIN and INNER JOIN both clauses work the same.||FULL OUTER JOIN and FULL JOIN both clauses work the same.|
from table1 INNER JOIN / JOIN table2
ON table1.column_name = table2.column_name;
from table1 LEFT OUTER JOIN / RIGHT OUTER JOIN /
FULL OUTER JOIN / FULL JOIN table2 ON
table1.column_name = table2.column_name;
Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.
- Difference between Natural join and Inner Join in SQL
- Full join and Inner join in MS SQL Server
- SQL | Join (Cartesian Join & Self Join)
- Difference between Left, Right and Full Outer Join
- SQL | Join (Inner, Left, Right and Full Joins)
- Difference between Natural join and Cross join in SQL
- Difference between Structured Query Language (SQL) and Transact-SQL (T-SQL)
- Left join and Right join in MS SQL Server
- Self Join and Cross Join in MS SQL Server
- SQL | EQUI Join and NON EQUI JOIN
- Difference between Nested Loop Join and Hash Join
- Difference between Nested Loop join and Sort Merge Join
- Difference between Hash Join and Sort Merge Join
- Difference between JOIN and UNION in SQL
- Difference between Anonymous Inner Class and Lambda Expression
- SQL | Difference between functions and stored procedures in PL/SQL
- Difference between T-SQL and PL-SQL
- Difference between SQL and T-SQL
- How to break an outer loop with PHP ?
- How to get all HTML content from DOMParser excluding the outer body tag ?
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to firstname.lastname@example.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.