Joins
Database contains many tables to store different conceptual data.
Actually we need to see the concept join in the relational database management system. Entities are created with normalization rules.
Thus, data for a single domain span across multiple tables in a normalization data model. For instance, the train ticket booking system uses Trains, Passenger and trip tables. It is a normalized structure where train details, passengers and their trip information are stored separately with key references.
Table key columns
Train – TrainId
passenger – Pid
Trip – TrainId, Pid, fair
Lets look into the types of Joins
- Inner join
- Left outer join
- Right outer join
- Full outer join
- Cross join
Inner join
It joins two tables and returns the matching records.
Select a.column1, a.column2
From table a
Inner join table2 b
On a.key_column = b.key_column;
Here, a and b are alias names for the tables. Will be used for shorthand names in the query.
Left join
Left outer join combines two tables. Whereas, it returns all the rows in the left table and the rows match on both sides. Finally, you will get the null on the right side for the unmatched rows.
The idea is, if you want to know the records that are not present in the right table can be filtered using Nul value.
Select a.*
From table a
Left outer join Table b
On a.key_column = b.key_column
Where b.Key_column is null:
Hence, this query output contains records from the left table that are not in the right table.
Right join
Right join combines both sides and focus on the right side of the table. It is similar to the left join. However, it returns all rows from the right table with matched rows and null on the left for the records that are not matched.
Select b.*
From table a
Right outer join table b
On a.key_column = b.key_column
Where a.key_column is null;
Full join
Full outer join takes everything from both sides. It is the combination of left and right table together and leave nothing out. Whereas, the results have null on either side if there is no match.
Select a.*, b.*
From table a
Full out join table b
On a.key_column = b._key_column;
Cross join
Cross join is a Cartesian product. There is no need for key columns in the join clause. It does produce the output by joining each row with every combination between two tables
Select a.*, b.*
From table a
Cross join table b;
There are other joins available in different products along with ANSI join.
Those are, natural join, inner join using(), cross apply, outer apply().
Learn by doing. Good luck.
SQL Server Rider 