SQL JOINS
Copyright Intellipaat. All rights reserved.
JOINS
By using joins, you can retrieve data from two or more tables based on logical relationships between
the tables. Joins indicate how SQL Server should use data from one table to select the rows in
another table.
Types of Joins -
INNER JOIN FULL JOIN
LEFT JOIN RIGHT JOIN
CROSS JOIN
Copyright Intellipaat. All rights reserved.
INNER JOIN
Inner Join returns records that have matching values in both tables. It is also known as a
simple join.
Copyright Intellipaat. All rights reserved.
INNER JOIN
Syntax - SELECT column_name FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
Create inner join of store1 and store2 where Average weekly sales is more than 50000
SELECT * FROM store1 INNER JOIN store2 ON [Link]=[Link] WHERE Avg_WS>50000
Copyright Intellipaat. All rights reserved.
LEFT JOIN
Left Join returns all the records from the left table and the matched records
from the right table.
Table A Table B
Copyright Intellipaat. All rights reserved.
LEFT JOIN
Syntax - SELECT column_name FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;
Find the Left Join of store1 and store2 where Average weekly sales is less than 50000
SELECT * FROM store1 LEFT JOIN store2 ON [Link]=[Link] WHERE Avg_WS<50000
Copyright Intellipaat. All rights reserved.
RIGHT JOIN
Right Join returns all the records from the right table and the matched records
from the left table.
Table A Table B
Copyright Intellipaat. All rights reserved.
RIGHT JOIN
Right JOIN returns all records from the right table, and the matching records from the left table.
Syntax - SELECT column_name FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;
Find the Right Join of store1 and store2 where Average weekly sales is less than 50000
SELECT * FROM store1 RIGHT JOIN store2 ON [Link]=[Link] WHERE Avg_WS<50000
Copyright Intellipaat. All rights reserved.
FULL JOIN
It returns all rows from the LEFT table and the RIGHT table with NULL values
in place where the join condition is not met.
Table A Table B
Copyright Intellipaat. All rights reserved.
FULL JOIN
FULL JOIN also known as FULL OUTER JOIN which returns all records when there is a match in left or right table records.
Syntax - SELECT column_name FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.
column_name where condition;
Find the store, Average CPI, Average fuel price and Average weekly sales using full Join on
store1 and store2 where average Weekly sales is between 10000 to 40000
SELECT [Link], Store1.Avg_CPI,
store1.Avg_Fuel_P, Store2.Avg_WS
FROM Store1 FULL OUTER JOIN
store2 ON [Link]=[Link]
WHERE Avg_WS BETWEEN 10000 AND
40000
Copyright Intellipaat. All rights reserved.
JOINS USING GROUPBY AND HAVING CLAUSES
Fetch the Store column from Features table, Dept column and Average of weekly sales for
each store from Sales table where average weekly sales is more 60000 using joins.
Select [Link], [Link], AVG(S.Weekly_Sales) as Avg_WS
from Features F
join Sales S
on
[Link]=[Link]
GROUP BY [Link], [Link]
HAVING AVG(S.Weekly_Sales)>10000
Copyright Intellipaat. All rights reserved.
JOINS USING GROUP BY AND ORDER BY CLAUSES
Fetch the Store column from Features table and Average of weekly sales for each store from
Sales table where average weekly sales must be displayed in descending order
Select [Link], AVG(S.Weekly_Sales) as Avg_WS
from Features F
join Sales S
on
[Link]=[Link]
GROUP BY [Link]
ORDER BY Avg_WS DESC
Copyright Intellipaat. All rights reserved.