Sql server


Why JOINS are needed?

Since the data has been split across into different tables, when the business needs the data to be retrieved which belongs to many tables, JOINS are needed. Database Diagram: The below are the tables which we are going to use it through over this article.














Table: Dept


 Table: Employee
 







Types of JOINS


1) CROSS JOIN
2) INNER JOIN
3) LEFT OUTER JOIN
4) RIGHT OUTER JOIN/
5) SELF JOIN


CROSS JOIN

A cross join returns the Cartesian product of the sets of records from the two joined tables. Generally it denotes as m x n, where m and n are tables.
Query:

SELECT d.DeptId, d.DeptName, e.EmployeeId, e.EmployeeName, e.DeptId FROM Dept d CROSS JOIN Employee e

How it works:
Every record in Dept table combines each and every record in Employee table.
Number of rows in Dept table: 4
Number of rows in Employee table: 5

m x n = 4 x 5 = 20 rows
Output:


INNER JOIN

An inner join combines the records from two tables based on the columns which makes related. For example, DeptId is the common field which relates Dept and Employee table.
Query:
Retrieve the Employee Id, Name with their respective department names.
SELECT 
    e.EmployeeId, 
    e.EmployeeName, 
    d.DeptName 
FROM Dept d 
INNER JOIN Employee e ON d.DeptId = e.DeptId
How it works:
  1. Retrieve the Cartesian product of Dept and Employee table which combines both the tables.
  2. Apply the predicates which extract only matched rows.
  3. Select the columns which you required from the result set. 


The above figure explains the only blue rounded rows are matched with the relationship column (DeptId).
Output:


OUTER JOIN:

The outer join does not require the matching record in both the joined tables. The joined table retains each record even there is no match exists. Outer joins are divided into three such as LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.

LEFT OUTER JOIN:

From the name, we could identify that there is some preference for the left hand side. The results of left outer join for tables Dept and Employee returns all the records from Dept table, even if the join condition does not find any match in Employee table.
Query:
SELECT 
    d.DeptId, 
    d.DeptName, 
    e.EmployeeId, 
    e.EmployeeName, 
    e.DeptId 
FROM Dept d 
LEFT OUTER JOIN Employee e ON d.DeptId = e.DeptId


Output:



The above picture explains that each DeptId matches with Employee table except DeptId 14 which implies the employee record return as NULL.
Question:
Let say, if I want to know the Departments which does not have any employees. How can I achieve it?
You can refer the above picture also.
Query: Option 1 by using JOIN
SELECT 
    d.DeptId, 
    d.DeptName 
FROM Dept d 
LEFT OUTER JOIN Employee e ON d.DeptId = e.DeptId 
WHERE 
    e.DeptId IS NULL
Good. We need to add the condition to check the value is NULL in WHERE Clause. You can solve in some other way also by using NOT EXISTS and NOT IN. How..? Check it out below:
Query: Option 2 by using NOT EXISTS
SELECT 
    DeptId, 
    DeptName 
FROM Dept d 
WHERE NOT EXISTS (SELECT 1 FROM Employee e WHERE d.DeptId = e.DeptId)
Do you want to know one more way?
Query: Option 3 by using NOT IN
SELECT 
    DeptId, 
    DeptName 
FROM Dept 
WHERE DeptId NOT IN(SELECT DeptId FROM Employee)
All the above three queries would return with the same result. But, you want to know which one is best. Well. Definitely, NOT IN is not a good choice. When you run with millions of records you can find the LEFT JOIN would be the better option compared with NOT EXISTS. This is extra point na, Send me Choclate. :-)

RIGHT OUTER JOIN:

A right outer join is almost same as left outer join except with the tables reversed. From the name, there is some preference for Right Side. The results of right outer join for tables Dept and Employee returns all the records from Employee, even if the join condition does not find any match in Dept table.
Query:
SELECT 
 d.DeptId, 
 d.DeptName, 
 e.EmployeeId, 
 e.EmployeeName, 
 e.DeptId 
FROM Dept d 
RIGHT OUTER JOIN Employee e ON d.DeptId = e.DeptId
Output:

FULL OUTER JOIN:

A Full outer join combines the result of both left and right outer joins which means both of the table’s rows will be retrieved and NULL will be filled up wherever missing matches.

SELF JOIN:

We have seen other types of joins by relating with different tables. There might be the situation where the table needs to relate with itself called Self Join. Joining a table itself will be useful when you want to compare the values in a column with another column values within a table.
To create a self-join, list a table twice and assign different aliases each time.
Question:
Find out the Manager Names for each Employee.
Query:
SELECT 
    Emp1.EmployeeId AS 'Employee ID', 
    Emp1.EmployeeName AS 'Employee Name', 
    Emp2.EmployeeId AS 'Manager ID',  
    Emp2.EmployeeName AS 'Manager Name' 
FROM EMPLOYEE Emp1 
INNER JOIN EMPLOYEE Emp2 ON Emp1.ManagerId = Emp2.EmployeeId
Output:




Now, if you look at the above picture, there are only four employees returned instead of five. Do you know the reason? Yeah. Since the Manager Id is NULL for the Employee ‘Anand’, which does not return. Why it’s happening? Because we used the INNER JOIN.
Since the requirement is displaying all the Employee’s Managers, we need to display the employee “Anand” also.
Query:
SELECT 
    Emp1.EmployeeId AS 'Employee ID', 
    Emp1.EmployeeName AS 'Employee Name', 
    ISNULL(Emp2.EmployeeId, 0) AS 'Manager ID', 
    ISNULL(Emp2.EmployeeName, 'Chief') AS 'Manager Name' 
FROM EMPLOYEE Emp1 
LEFT JOIN EMPLOYEE Emp2 ON Emp1.ManagerId = Emp2.EmployeeId
Since we have seen the Group by Clause in previous article, let me add one more Query which includes JOIN with Group by clause. Click here to access the GROUP BY clause article.
Question:
Retrieve the number of Employees working for each Dept, and the display order should be descending order of the employee count.
Query:
SELECT 
 d.DeptName,
 COUNT(e.DeptId) AS 'Number of Employees'
FROM Dept d
LEFT JOIN Employee e ON d.DeptId = e.DeptId
GROUP BY 
d.DeptId, d.DeptName
ORDER BY COUNT(e.DeptId) DESC
Conclusion:
I believe this article which helps you to understand about Inner Join, Left Outer Join, Right Outer Join, Full Outer Join, and self join. Try to write as many queries as possible to understand more. You would enjoy it…! Cheers...

-----------------------------------------------------------------------------------------------------------------------------


Difference between Stored procedure and User Functions
 


In many situation you can do the same task using either a stored procedure or a function.
Fundamental difference between Stored procedure vs User Functions:
  • Procedure may return none or more values.Function must always return one value either a scalar value or a table.
  • Procedure have input,output parameters.Functions have only input parameters.
  • Stored procedures are called independently by EXEC command whereas Functions are called from within SQL statement.
  • Functions can be called from procedure.Procedures cannot be called from function.
  • Exception can be handled in Procedure by try-catch block but try-catch block cannot be used in a function.(error-handling)
  • Transaction management possible in procedure but not in function.
 

-----------------------------------------------------------------------------------------------------------------------------


Difference between Truncate and Delete in SQL
 
Truncate an Delete both are used to delete data from the table. These both command will only delete data of the specified table, they cannot remove the whole table data structure.Both statements delete the data from the table not the structure of the table.
  • TRUNCATE is a DDL (data definition language) command whereas DELETE is a DML (data manipulation language) command.
  • You can use WHERE clause(conditions) with DELETE but you can't use WHERE clause with TRUNCATE .
  • You cann't rollback data in TRUNCATE but in DELETE you can rollback data.TRUNCATE removes(delete) the record permanently.

No comments:

Post a Comment

Subscribe to: Posts (Atom)