Join is used to retrieve data from two or more database tables. Using SQL Joins we can combine records from two or more tables in a database. There are different kinds of join in SQL. The joins are performed in the WHERE clause. Several operators can be used to join tables, such as =, <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT. Joins can also be used in other clauses such as GROUP BY, WHERE, SUB QUERIES, AGGREGATE FUNCTIONS etc. Let’s see one Example to understand Join.
Table 1 − STUDENT Table
Id | Name | Batch | Subject | Marks |
1 | Rahul | A | Maths | 99 |
2 | Kirti | B | Science | 87 |
3 | James | C | English | 99 |
4 | Uday | A | English | 85 |
5 | Angel | B | Science | 83 |
Table 2 − RESULT Table
RID | STUDENT_ID | Grade |
102 | 3 | A |
101 | 2 | B |
103 | 4 | B |
let us join these two tables in our SELECT statement
SELECT ID, NAME, Batch, Grade
FROM STUDENT, RESULT
WHERE STUDENT.ID = RESULT.STUDENT_ID;
This would produce the following result.
Id | Name | Batch | Grade |
3 | James | C | A |
2 | Kirti | B | B |
4 | Uday | A | B |
Different Types of SQL JOINs
- Inner Join – This Join returns rows when there is a match in both tables.
- Outer Join – There are three types of outer join.
- Left Join − This Join returns all rows from the left table, even if there are no matches in the right table.
- Right Join − This Join returns all rows from the right table, even if there are no matches in the left table.
- Full Join − This Join returns rows when there is a match in one of the tables.
- Self Join − This Join is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
- Cartesian Join − This Join returns the Cartesian product of the sets of records from the two or more joined tables.
Example of Joins
Consider two table College and Hostel for understands types of join and difference between joins.
Table 1: College
College | ||
Name | Id | Department |
Jons | A1 | Computer |
Thomas | A2 | Computer |
Greek | A3 | IT |
Table 2: Hostel
Hostel | ||
Name | Hostel_Name | Room No |
Thomas | Hilltop | 101 |
Greek | Academic | 102 |
Luis | Art | 103 |
Output of Inner Join
Inner Join | ||||
Name | Id | Department | Hostel_Name | Room No |
Thomas | A2 | Computer | Hilltop | 101 |
Greek | A3 | IT | Academic | 102 |
Output of Left Outer Join
Left Outer Join | ||||
Name | Id | Department | Hostel_Name | Room No |
Jons | A1 | Computer | Null | Null |
Thomas | A2 | Computer | Hilltop | 101 |
Greek | A3 | IT | Academic | 102 |
Output of Right Outer Join
Right Outer Join | ||||
Name | Id | Department | Hostel_Name | Room No |
Thomas | A2 | Computer | Hilltop | 101 |
Greek | A3 | IT | Academic | 102 |
Luis | Null | Null | Art | 103 |
Output of Full Outer Join
Full Outer Join | ||||
Jons | A1 | Computer | Null | Null |
Thomas | A2 | Computer | Hilltop | 101 |
Greek | A3 | IT | Academic | 102 |
Luis | Null | Null | Art | 103 |
In short SQL join is used to improve data integrity and reduce the number of queries requirements. Using join easily combine data from multiple tables and get data as per requirements from different tables.