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;
Don’t stop now and take your learning to the next level. Learn all the important concepts of Data Structures and Algorithms with the help of the most trusted course: DSA Self Paced. Become industry ready at a student-friendly price.
- Difference between Left, Right and Full Outer Join
- Inner Join vs Outer Join
- Difference between Natural join and Cross join in SQL
- Difference between Natural join and Inner Join in SQL
- SQL | Join (Cartesian Join & Self Join)
- Difference between JOIN and UNION in SQL
- Difference between Lossless and Lossy Join Decomposition
- Join statement in JCL
- SQL Join vs Subquery
- Join algorithms in Database
- How to use .join() with push and replace together?
- SQL | Join (Inner, Left, Right and Full Joins)
- Should I join a mass recruiter or a startup?
- strings.Join() Function in Golang With Examples
- Lossless Join and Dependency Preserving Decomposition
- Join operation Vs Nested query in DBMS
- Differences between wait() and join() methods in Java
- What is PJNF(Project-Join Normal Form)?
- Join two text columns into a single column in Pandas
- How to break an outer loop with PHP ?
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to email@example.com. 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.