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.
Syntax:
select * from table1 INNER JOIN table2 on table1.column_name = table2.column_name;
OR
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. |
7. | SQL Syntax: select * from table1 INNER JOIN / JOIN table2 ON table1.column_name = table2.column_name; | SQL Syntax: select * 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.