- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Difference Between Inner Join and Outer Join in SQL
In this post, we will understand the difference between inner join and outer join in SQL.
Inner Join
The clause used is ‘INNER JOIN’ and ‘JOIN’.
It returns the combined tuple of two or more tables.
When no attributes are common, the result is empty.
If the number of tuples is more, then ‘INNER JOIN’ works quickly in comparison to ‘OUTER JOIN’.
It is used when detailed information about a specific attribute is required.
The ‘JOIN’ and ‘INNER JOIN’ work in the same manner.
Syntax
SELECT * FROM table_1 INNER JOIN / JOIN table_2 ON table_1.column_name = table_2.column_name;
Outer Join
It returns the combined tuple of a specified table.
It is returned even when the ‘JOIN’ condition fails.
The clauses LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN can be used.
It doesn’t depend on the common attributes.
If the attribute is blank, NULL is placed instead of the blank.
The ‘OUTER JOIN’ is slow in comparison to ‘INNER JOIN’.
It is used when complete information is required.
FULL OUTER JOIN and FULL JOIN clauses work in the same manner.
Syntax
SELECT * FROM table_1 LEFT OUTER JOIN / RIGHT OUTER JOIN / FULL OUTER JOIN / FULL JOIN table_2 ON Table_1.column_name = table_2.column_name;
- Related Articles
- Difference between Inner and Outer join in SQL
- INNER JOIN vs FULL OUTER JOIN vs LEFT JOIN vs RIGHT JOIN in PostgreSQL?
- Difference between Join and union in SQL
- Difference Between Left, Right and Full Outer Join
- Usage and syntax of INNER and OUTER JOIN in DB2
- How to do an inner join and outer join of two data frames in R?
- How can we distinguish between MySQL CROSS JOIN and INNER JOIN?
- Python - Merge Pandas DataFrame with Outer Join
- Python - Merge Pandas DataFrame with Inner Join
- Python - Merge Pandas DataFrame with Right Outer Join
- Python - Merge Pandas DataFrame with Left Outer Join
- How can we convert subqueries to INNER JOIN?
- Joining cobk and coep table using Inner join taking too long
- Compare two tables and return missing ids using MySQL LEFT OUTER JOIN
- How can we create a MySQL view with INNER JOIN?
