What is a join?! This is a clause that is used to combine rows from 2/more tables based on a related column between them. There are several types of joins. They include;
-Inner join
- Right join
- Left join
- Full join/ full outer join
- self-join. All the fore-mentioned are types of joins that have unique characteristics to one another that set them apart and provide thereby a different result.
Inner join
This a type of join that when called returns records that have matching values in both tables, each table that is, “table1” and “table2”. The inner join is the default type of join. Below, I give the format that it should take and an example in case.
SELECT column_name(s)
FROM table1
INNER JOIN table1 on table2.column_name=table2.column_name;
The above syntax returns matching values that are in both table1 and table2. To note is that either inner join or join that is passed on the phrase a record will be returned as a result of inner join being the default join.
Left Join
The left join returns all records from the left table(table1) and matching record from right table(table2). It follows the following syntax;
SELECT column_name(s)
FROM table1
Left join table2 on table1.column_name=table2.column_name;
This now thereby returns all the records from table1 and matching values from table2. In the instance that there is a lack of matching records in table2, then, only records from table1 are returned.
Right join
The right join returns all records from the right table(table2) and matching records in the left table(table1). Of essence to note is that in the instance where there are no matching records from the left table(table1), then there are no columns or rather records that’ll be returned from the left table. It follows the following syntax;
SELECT column_name(s)
FROM table1
Right join table2 on table1.column_name=table2.column_name;
This therefore returns all records from table2 and matching records from table1. In the case there are no matching values from table1 then there will be no records from table1 returned, only table2 records are thereby returned.
Outer join
The outer join also known as the full outer join returns all records when there is a match in either left(table1) or right(table2) records. This type of join returns all records as long as there is a matching column so to say in either of the tables. It follows the following syntax;
SELECT column_name(s)
FROM table1
OUTER JOIN table2
ON table1.column_name = table2.column_name;
This phrase returns all rows from both the left and right table provided there’s a match between the tables and thus they are combined as a result. In the instance there happens to be a row in the left table that has no matching row in the right table, then the right table will have null values in the result.
The outer join is useful when you need to see all records from both tables regardless of whether they have a match or not.
To note is the difference between the full outer join and the full inner join. The inner join returns only records where the join condition is met in both and where a record in on table doesn’t have a corresponding match in the other table then that record is excluded from the result unlike in the case of an outer join where it would return “NULL”.
In conclusion, joins are a vital part in ensuring the success of data analysis. They bind relational data together and are thus important for the following uses so to say;
- Connecting and transforming tables.
- Handling data inconsistencies.
- Extracting useful insights with precision.
Mastering SQL joins is of great essence. This ensures that one has meaningful results through their interactions with relational databases, but not only limited to but including joins mastery.
Top comments (1)
Well presented! 👏