Inner Join: Return rows/records that have matching values in both the tables. Inner join is going to return all the rows of colour_id column which is comman/present in both the tables.
Consider a example of two tables named product_tbl and colour_tbl in which colour_id column is present in both the tables.
Matching values are (1,2,3)
select * from Producttbl as p
inner join Colourtbl as c on p.ColourId = c.ColourId
Output
Explanation: It will scan the table1(producttbl) and find colour_id 1 then it will check if it is present in table2(colourtbl) or not. If it is not present in colourtbl then colour_id will 1 will be skipped and scanning will be moved to next colour_id. If it present then it will show as output and details will be picked like (productId,productName,colourId) from producttbl and details like(colourId, colourName) will be picked from colourtbl and return the details of both the table one by one.
Left Join: Return all rows/records from the left table.
Consider a example of two tables named product_tbl and colour_tbl in which colour_id column is present in both the tables.
Left join is going to return all the rows of colour_id column which is present in left table or table1. It doesn't matter the mattched colour_id is present in colourtbl(table2) or not. If it is present in colourtbl then it will fetched the details like (colourId, colourName). And if it is not present in colourtbl then values like(colourId, colourName) will be Null as output.
select * from Producttbl as p
left join Colourtbl as c on p.ColourId = c.ColourId
- Colour_id (1,2,3) is also present in table2(colourtbl). Hence, those details of colourId is fetched.
- Colour_id (10,33) is not present in colourtbl, so null is there as colourId and colourName values.
Right Join: Return all rows/records from the right table.
Consider a example of two tables named product_tbl and colour_tbl in which colour_id column is present in both the tables.
Right join is going to return all the rows of colour_id column which is present in right table or table2. It doesn't matter the mattched colour_id is present in producttlb(table1) or not.
- If it is present in product_tbl then it will fetched the details like (product_id,product_name,colour_id).
- And if it is not present in product_tbl then values like(product_id,product_name,colour_id) will be Null as output.
select * from Producttbl as p
right join Colourtbl as c on p.ColourId = c.ColourId
Colour_id (1,2,3) is also present in product_tbl and it's details has been fetched. But colour_id (4,5) is not present in product_tbl. Hence, null is there as Product_id, product_name, colour_id values.
Full Join: Return all the records from left and right table.
select * from Producttbl as p
full join Colourtbl as c on p.ColourId = c.ColourId
- Colour_id(1,2,3) is matched in both tables. Heance all the details are fetched from both tables.
- Colour_id (10,33) is present in only product_tbl. Hence, product details like (product_id, product_name, colour_id) are fetched from product table. And details like( colour_id, colour_name) is set to null.
- Colour_id(4,5) is present in only colour_tbl. Hence, colour details like (colour_id, colour_name) are fetched from colour table. And details like (product_id, product_name, colour_id) is set to null.
Fetch ColourId which is only present in product_tbl and not in colour_tbl
Left join will fetch all the details from product table regardless of those colour_id is present in colour table or not. If that colour_id is present in the colour table it will fetch details like (colour_id, colour_name). If it is not present in colour_tbl details like (colour_id, colour_name) will be set to null.
Hence, we can write a left query which will fetch all the details from the product table and then removed all the matching rows which is present in both the tables.
select * from Producttbl as p
left join Colourtbl as c on p.ColourId = c.ColourId
How to know which are the rows (colour_id) which is comman in both the tables?
- Simple, if the matched colour_id is found in colour_tbl it will basically fetch the record details like (colour_id, colour_name). As from the above output 1,2 and 3 details are matched in colour_table.
- And colour_id(10,33) doesn't matched in colour_tbl it is only present in product_tbl. So, it's values (colour_id, colour_name) is set to null.
So, can we write a some kind of query which left join product table and then fetch only those records whose colour_tbl (colour_id, colour_name) is set to null?
select * from Producttbl as p
left join Colourtbl as c on p.ColourId = c.ColourId
where c.ColourId is null
Fetch ColourId which is only present in colour_tbl and not in product_tbl
As we fetched all colour_id from product_tbl using left join and then removed the colour_id which is comman in both tables we get all colour_id which is only present in product_tbl.
Same way, we are going to apply using right join to fetch all colour_id which is only present in colour_tbl.
select * from Producttbl as p
right join Colourtbl as c on p.ColourId = c.ColourId
where p.ColourId is null
Fetch all colour_id which is there in only product_tbl or colour_tbl but should not be in both the tables. Remove common/matched colour_id.
Using full join first of all we will fetch all records from both tables.
- Then if colour_tbl details (colour_id, colour_name) are null it basically means that colour_id is only present in product table.
- If product_tbl details (product_id,product_name,colour_id) are null it basically means that colour_id is only present in colour_tbl.
select * from Producttbl as p
full join Colourtbl as c on p.ColourId = c.ColourId
where p.ColourId is null or c.ColourId is null
SQL Script:
CREATE TABLE [dbo].[Colourtbl](
[ColourId] [int] NULL,
[ColourName] [nvarchar](50) NULL
)
CREATE TABLE [dbo].[Producttbl](
[ProductId] [int] NULL,
[ProductName] [nvarchar](50) NULL,
[ColourId] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Colourtbl] ([ColourId], [ColourName]) VALUES (1, N'Blue')
GO
INSERT [dbo].[Colourtbl] ([ColourId], [ColourName]) VALUES (2, N'Red')
GO
INSERT [dbo].[Colourtbl] ([ColourId], [ColourName]) VALUES (3, N'Black')
GO
INSERT [dbo].[Colourtbl] ([ColourId], [ColourName]) VALUES (4, N'Pink')
GO
INSERT [dbo].[Colourtbl] ([ColourId], [ColourName]) VALUES (5, N'Yellow')
GO
INSERT [dbo].[Producttbl] ([ProductId], [ProductName], [ColourId]) VALUES (1, N'Jeans', 1)
GO
INSERT [dbo].[Producttbl] ([ProductId], [ProductName], [ColourId]) VALUES (2, N'Frock', 2)
GO
INSERT [dbo].[Producttbl] ([ProductId], [ProductName], [ColourId]) VALUES (3, N'Top', 3)
GO
INSERT [dbo].[Producttbl] ([ProductId], [ProductName], [ColourId]) VALUES (4, N'Pillow', 2)
GO
INSERT [dbo].[Producttbl] ([ProductId], [ProductName], [ColourId]) VALUES (5, N'Teddy', 10)
GO
INSERT [dbo].[Producttbl] ([ProductId], [ProductName], [ColourId]) VALUES (6, N'Balloon', 33)
GO