Join is a very basic and sometimes confusing concept in SQL. Use joins when we need to find queries that involve properties of multiple tables that have at least one property in common. Therefore, the need for a Join is clear. There are different types of connections for different purposes. The original
In SQL Joins
Suppose we have two tables, one named STUDENT and the other named TEACHER.
The first table “STUDENT”: the reference details of the STUDENT, the second table” TEACHER”: the details of the school teachers and courses.
STUDENT
| ClassID | Name | House address |
|---|---|---|
| 101 | raj | xyz |
| 201 | Varun | byc |
| 301 | Chittiz | abc |
| 401 | Hector horse | def |
TEACHER
| Teacher id | Class name | ClassID |
|---|---|---|
| 1 | First of all, | 101 |
| 2 | The second | 201 |
| 3 | The third | 301 |
| 4 | The fourth | 401 |
In the second table, “ClassID” is a foreign key used as a reference to the first table in the second table.
Now, if we want to find the teacher with ID 1; Of the student’s name, we need to find the link to the above form because it requires us to collect information on both forms. Therefore, join should only be used if both tables have at least one property in common (ClassID in this case), and we need to find a solution for queries involving properties of both tables.
The type of the Join
There are basically four types of Join: Inner, Outer, Left and Right Join. Each of the mentioned links is explained below.
1, Inner Join
Let’s consider the following two tables, the first with the name Country(which holds the ids of different countries) and the other with the name State(which holds the various states of those countries).
COUNTRY
| CountryId | CountryName |
|---|---|
| 1 | China |
| 2 | India |
| 3 | The United States |
STATE
| StateId | CountryId | StateName |
|---|---|---|
| 01 | 2 | GOA |
| 02 | 2 | RAJASTHAN |
| 03 | 5 | Kathmandu |
| 04 | 6 | Islamabad |
select * from COUNTRY
inner join STATE
on COUNTRY.CountryId=STATE.CountryId
The command mentioned above applies the inner join on both tables, since the common property is the Country ID, we already apply the join on the same table.
An inner join returns all matching values in both tables. Here, in table State, because the only CountryId value that matches in the country table is {CountryId = 2}, as a result of the inner join, we get the following result:
| CountryId | CountryName | StateId | StateName |
|---|---|---|---|
| 2 | India | 01 | GOA |
| 2 | India | 02 | RAJASTHAN |
2. Right Join
Right (or right outer join), on the other hand, shows data that is common to both tables, as well as data that is present in the right table (excluding only).
This basically means that the data for the entire right table will be displayed when the right join is applied.
If there is no match in the left table, NULL is displayed.
Example:
COUNTRY
| CountryId | CountryName |
|---|---|
| 1 | China |
| 2 | India |
| 3 | The United States |
STATE
| StateId | CountryId | StateName |
|---|---|---|
| 01 | 2 | GOA |
| 02 | 2 | RAJASTHAN |
| 03 | 5 | Kathmandu |
| 04 | 6 | Islamabad |
select * from COUNTRY
right join STATE
on COUNTRY.CountryId=STATE.CountryId
The command mentioned above applies a right join on both tables because the common property is CountryId; We’ve already applied connections to CountryId itself.
The right table is the second table we reference.
With the correct join applied, we get the following table:
| CountryId | CountryName | StateId | StateName |
|---|---|---|---|
| 2 | India | 01 | GOA |
| 2 | India | 02 | RAJASTHAN |
| 5 | NULL | 03 | Kathmandu |
| 6 | NULL | 04 | Islamabad |
In the result, values that clearly describe values in the left table and absolutely no matching values in the right object are not displayed. Display only those values of the left table that have attributes in common with the right object. All values in the right table are displayed. Rows in the right table that do not match are NULL.
3. Left Join
A left join (or left outer join), on the other hand, shows data that is common to both tables, as well as data that is present in the left table (excluding only).
This basically means that the entire left table data will be displayed when the left join is applied.
If there is no match in the left table, NULL is displayed.
COUNTRY
| CountryId | CountryName |
|---|---|
| 1 | China |
| 2 | India |
| 3 | The United States |
STATE
| StateId | CountryId | StateName |
|---|---|---|
| 01 | 2 | GOA |
| 02 | 2 | RAJASTHAN |
| 03 | 5 | Kathmandu |
| 04 | 6 | Islamabad |
select * from COUNTRY
left join STATE
on COUNTRY.CountryId =STATE.CountryId
The command mentioned above applies a Left Join on both tables because the common attribute is CountryId; We’ve already applied connections to Countryid itself.
The left table is the first table we refer to.
For the application of the left join we get the following table:
| CountryId | CountryName | Stateid | Statename |
|---|---|---|---|
| 1 | China | NULL | NULL |
| 2 | India | 01 | GOA |
| 2 | India | 02 | RAJASTHAN |
| 3 | The United States | NULL | NULL |
In the result, the value that clearly indicates that the left matching value is not displayed in the right column is not displayed. Only those properties shown in the right column have attributes in common with the attributes on the left. All values in the left table are displayed. Rows in the left table that do not match in the right table are NULL
4. Full Outer Join
As the name implies, the Full Outer Join shows all the contents of both tables. Full Outer Join returns all matches in both tables, regardless of whether the other tables match.
COUNTRY
| CountryId | CountryName |
|---|---|
| 1 | China |
| 2 | India |
| 3 | The United States |
STATE
| StateId | CountryId | StateName |
|---|---|---|
| 01 | 2 | GOA |
| 02 | 2 | RAJASTHAN |
| 03 | 5 | Kathmandu |
| 04 | 6 | Canada |
select * from COUNTRY
full outer join STATE
on COUNTRY.CountryId=TEACHER.CountryId
The command mentioned above applies Full Outer Join on both tables because the common attribute is CountryId; We’ve already applied connections to Countryid itself.
For Full Outer Join applications, we get the following table:
| CountryId | CountryName | Stateid | Statename |
|---|---|---|---|
| 1 | China | NULL | NULL |
| 2 | India | 01 | GOA |
| 2 | India | 02 | RAJASTHAN |
| 3 | The United States | NULL | NULL |
| NULL | NULL | 03 | Kathmandu |
| NULL | NULL | 04 | Islamabad |
This Join will result in all rows. When there is no match, we get NULL.
Joins are important for working with tables in SQL, and the above description really details how each table is used.
If you have any queries related to SQL connections, comment below.
You might also like:
- Difference between Primary Key and Foreign Key
- Dynamic SQL in DBMS
- Joining Three or More Tables in SQL
- Difference between SQL and PL/SQL
- Introduction to SQL