JOINS:-
SQL Server provides a method of retrieving data from more than one table using Joins.
“ JOINS can be defined as an operation that includes retrieval of data from more than one table at a time”
Syntax :
SELECT col-Name,col_Name(..col_Names)
FROM table_name , table_name (,..table_name)
WHERE table_name.ref_col_name join_operator table_name.ref_col_name
Where
Col_Name :- Specifies the name of columns from one or more than one table
Table_name:- Specifies the name of the tables from which data is to be retrieved
Ref_col_name:- Specifies the name of the columns that is used to combine two tables using common keys from the respective tables.
Join_operator:- Specifies the operation used to join the tables
Types Of JOINS :-
Inner Join
Outer Join
- Left Outer Join
- Right Outer Join
Cross Join
Equi Join
Natural Join
Self Join
- Inner Join :-
Inner join shows the matches only when they exists in both tables
Example
There are two tables Customers and Orders and INNER JOIN is made on common reference column i.e. on Customers.custId = Orders.custId
SELECT Customers.*,Orders.*
FROM Customers Inner Join Orders
ON Customers.custId = Orders.custId
-So, This SQL only gives you results with customers who have orders.
If the customer does not have orders it will not display that record.
An Inner join will take two tables & join them together based on values in common columns (Linking Field) from each table.
For Example
There are two tables Employee and Department-
EmpId / EmpName / DeptId1 / Smith / 1
2 / Jack / 2
3 / Jones / 2
4 / Andrew / 3
5 / Dave / 5
6 / Joseph
DeptId / Dept Name
1 / HR
2 / Finance
3 / Security
4 / Sports
5 / Housekeeping
6 / Electrical
Example 1 :- To retrieve only the information about those employees who are assigned to a department.
SELECT Employee.EmpID, Employee.EmpName,Department.DeptName
FROM Employee Inner Join Department
ON Employee.DeptId = Department.DeptId
The ResultSet will be :-
EmpId / EmpName / DeptNamee1 / Smith / HR
2 / Jack / Finance
3 / Jones / Finance
4 / Andrew / Security
5 / Dave / HouseKeeping
Example 2:- Retrieve only the information about departments to which at least one employee is assigned.
SELECT Department.DeptId ,Department.DeptName
FROM Department Inner Join Employee
ON Employee.DeptId = Department.DeptId
The ResultSet will be :-
1 / HR
2 / Finance
3 / Security
5 / Housekeeping
------
For Example:
Suppose there are two tables “Customers” and “Orders” as
Customers Orders
ProdId / ProdName / CustIdp1 / Keyboard / 1
P2 / Mouse / 3
P3 / USB / 3
P4 / DVD / 2
p5 / CD / 4
CustId / Name
1 / Rajesh
2 / Shravan
3 / Sarang
4 / Bisen
Inner Join return all the rows from both tables where there is a match .
If there are rows in customers tables that do not have matches in orders, those rows will not be listed.
Ex 1: In above table ,who has ordered a product and what did they order?
Then for this, following query gives the exact expected results
SELECT Customers. Name, Orders. ProdName
FROM Customers Inner Join Orders’
ON Customers.custId = Orders.CustId
Result set is –
Name / ProdNameRaj / Keyboard
Shravan / CD
Sarang / Mouse
Sarang / USB
Bisen / DVD
------
We are going to illustrate our SQL JOIN example with the following 2 tables:
Customers:
CustId / FirstName / LastName / Email / DOB / Phone1 / Sarang / Dalal / / 2/4/1968 /
9421869223
2 / Prasad / Kawate / / 4/4/1974 /
99224200283
3 / Rajeev / Singh / / 5/24/1978 / 9371499558
4 / Jeffy / Thomas / / 20/10/1980 /
9890184503
CustId / Date / SaleAmount
2 / 5/6/2004 / 100.00
1 / 5/7/2004 / 200.00
3 / 5/7/2004 / 300.00
3 / ####### / 400.00
4 / ####### / 500.00
Sales:
As you can see those 2 tables have common field called CustID and thanks to that we can extract information from both tables by matching their CustrID columns.
Consider the following SQL statement:
SELECT Customers.FirstName, Customers.LastName, SUM(Sales.SaleAmount) AS SalesPerCustomer
FROM Customers, Sales
WHERE Customers.CustoID = Sales.CustoID
GROUP BY Customers.FirstName, Customers.LastName
The SQL expression above will select all distinct customers (their first and last names) and the total respective amount of dollars they have spent.
The SQL JOIN condition has been specified after the SQL WHERE clause and says that the 2 tables have to be matched by their respective CustoID columns.
Here is the result of this SQL statement:
FirstName / LastName / SalesPerCustomersSarang / Dalal / 200.00
Prasad / Kawte / 100.00
Rajeev / Singh / 700.00
Jeffy / Thomas / 500.00
The SQL statement above can be re-written using the SQL JOIN clause like this:
SELECT Customers.FirstName, Customers.LastName, SUM(Sales.SaleAmount) AS SalesPerCustomer
FROM Customers JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
GROUP BY Customers.FirstName, Customers.LastName
If you don't put INNER or OUTER keywords in front of the SQL JOIN keyword, then INNER JOIN is used. In short "INNER JOIN" = "JOIN" (note that different databases have different syntax for their JOIN clauses).
The INNER JOIN will select all rows from both tables as long as there is a match between the columns we are matching on. In case we have a customer in the Customers table, which still hasn't made any orders (there are no entries for this customer in the Sales table), this customer will not be listed in the result of our SQL query above.
If the Sales table has the following rows:
CustId / Date / SaleAmount2 / 5/6/2004 / 100.00
1 / 5/7/2004 / 200.00
And we use the same SQL JOIN statement from above:
SELECT Customers.FirstName, Customers.LastName, SUM(Sales.SaleAmount) AS SalesPerCustomer
FROM Customers JOIN Sales
ON Customers.CustoID = Sales.CustoID
GROUP BY Customers.FirstName, Customers.LastName
We'll get the following result:
FirstName / LastName / SalesPerCustomersPrasad / Kawte / 100.00
Sarang / Dalal / 200.00
Even though Rajeev and Jeffy are listed as customers in the Customers table they won't be displayed because they haven't purchased anything yet.
- Outer Join:-
If you want to display all the customers and their sales, no matter if they have ordered something or not? We’ll do that with the help of SQL OUTER JOIN clause.
The second type of SQL JOIN is called SQL OUTER JOIN and it has 2 sub-types called LEFT OUTER JOIN and RIGHT OUTER JOIN.
The LEFT OUTER JOIN or simply LEFT JOIN (you can omit the OUTER keyword in most databases), selects all the rows from the first table listed after the FROM clause, no matter if they have matches in the second table.
If we slightly modify our last SQL statement to:
SELECT Customers.FirstName, Customers.LastName, SUM(Sales.SaleAmount) AS SalesPerCustomer
FROM Customers LEFT JOIN Sales
ON Customers.CustoID = Sales.CustoID
GROUP BY Customers.FirstName, Customers.LastName
and the Sales table still has the following rows:
CustId / Date / SaleAmount2 / 5/6/2004 / 100.00
1 / 5/7/2004 / 200.00
The result will be the following:
FirstName / LastName / SalesPerCustomersSarang / Dalal / 200.00
Prasad / Kawte / 100.00
Rajeev / Singh / NULL
Jeffy / Thomas / NULL
As you can see we have selected everything from the Customers (first table). For all rows from Customers, which don’t have a match in the Sales (second table), the SalesPerCustomer column has amount NULL (NULL means a column contains nothing).
The RIGHT OUTER JOIN or just RIGHT JOIN behaves exactly as SQL LEFT JOIN, except that it returns all rows from the second table (the right table in our SQL JOIN statement).