SQL Course
JOIN
An SQL Join statement is used to combine data or rows from two or more tables based on a common field between them.
Different types of Joins are:
INNER JOIN
The INNER JOIN keyword selects all rows from both the tables as long as the condition satisfies. This keyword will create the result-set by combining all rows from both the tables where the condition satisfies i.e value of the common field will be same.
SELECT tblperson.name,tblperson.email,tblperson.city,tblgender.gender FROM tblperson INNER JOIN tblgender ON tblperson.genderid=tblgender.id;
For example, consider the following two tables.
Student Table
EnrollNo StudentName Address
1000 geek1 geeksquiz1
1001 geek2 geeksquiz2
1002 geek3 geeksquiz3
StudentCourse Table
CourseID EnrollNo
1 1000
2 1000
3 1000
1 1002
2 1003
Following is join query that shows names of students enrolled in different courseIDs.
SELECT StudentCourse.CourseID, Student.StudentName FROM StudentCourse INNER JOIN Customers ON StudentCourse.EnrollNo = Student.EnrollNo ORDER BY StudentCourse.CourseID;
The above query would produce following result.
CourseID StudentName
1 geek1
1 geek2
2 geek1
2 geek3
3 geek1
LEFT JOIN
This join returns all the rows of the table on the left side of the join and matching rows for the table on the right side of join. The rows for which there is no matching row on right side, the result-set will contain null. LEFT JOIN is also known as LEFT OUTER JOIN
SELECT tblperson.name,tblperson.email,tblperson.city,tblgender.gender FROM tblperson LEFT JOIN tblgender ON tblperson.genderid=tblgender.id;
RIGHT JOIN
RIGHT JOIN is similar to LEFT JOIN. This join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of join. The rows for which there is no matching row on left side, the result-set will contain null. RIGHT JOIN is also known as RIGHT OUTER JOIN.
SELECT tblperson.name,tblperson.email,tblperson.city,tblgender.gender FROM tblperson RIGHT JOIN tblgender ON tblperson.genderid=tblgender.id;
FULL JOIN
FULL JOIN creates the result-set by combining result of both LEFT JOIN and RIGHT JOIN. The result-set will contain all the rows from both the tables. The rows for which there is no matching, the result-set will contain NULL values.
SELECT tblperson.name,tblperson.email,tblperson.city,tblgender.gender FROM tblperson FULL JOIN tblgender ON tblperson.genderid=tblgender.id;
CROSS JOIN
Cross join produces a result set which is the number of rows in the first table multiplied by a number of rows in the second table if no WHERE clause is used along with Cross join. This kind of result is known as Cartesian Product. If suppose, Where clause is used in cross join then the query will work like an Inner join.
SELECT tblperson.name,tblperson.email,tblperson.city,tblgender.gender FROM tblperson CROSS JOIN tblgender
What is the difference between INNER JOIN and RIGHT JOIN?
INNER JOIN returns only the matching rows between the tables involved in the JOIN, where as RIGHT JOIN returns all the rows from the right table including the NON-MATCHING rows.
What is the difference between INNER JOIN and FULL JOIN?
FULL JOIN returns all the rows from both the left and right tables including the NON-MATCHING rows.
What is the Difference between INNER JOIN and JOIN?
There is no difference they are exactly the same. Similarly there is also no difference between
LEFT JOIN and LEFT OUTER JOIN
RIGHT JOIN and RIGHT OUTER JOIN
FULL JOIN and FULL OUTER JOIN
Retrieve only the non matching rows from the left table
select tblperson.name,tblperson.email,tblperson.city,tblgender.gender from tblperson left Join tblgender on tblperson.genderid=tblgender.id where tblgender.id is null
Retrieve only the non matching rows from the right table
select tblperson.name,tblperson.email,tblperson.city,tblgender.gender from tblperson right Join tblgender on tblperson.genderid=tblgender.id where tblperson.genderid is null
Retrieve only the non matching rows from both the left and right table
select tblperson.name,tblperson.email,tblperson.city,tblgender.gender from tblperson full outer Join tblgender on tblperson.genderid=tblgender.id where tblgender.id is null or tblperson.genderid is null
SELF JOIN
A MANAGER is also an EMPLOYEE. Both the, EMPLOYEE and MANAGER rows, are present in the same table. Here we are joining tblEmployee with itself using different alias names, E for Employee and M for Manager. We are using LEFT JOIN, to get the rows with ManagerId NULL. You can see in the output TODD's record is also retrieved, but the MANAGER is NULL. If you replace LEFT JOIN with INNER JOIN, you will not get TODD's record.
SELECT E.Name as Employee, M.Name as Manager FROM Employee E LEFT JOIN Employee M ON E.id = M.mid
In short, joining a table with itself is called as SELF JOIN. SELF JOIN is not a different type of JOIN. It can be classified under any type of JOIN - INNER, OUTER or CROSS Joins. The above query is, LEFT OUTER SELF Join.
Inner Self Join Employee table
SELECT E.Name as Employee, M.Name as Manager FROM Employee E INNER JOIN Employee M ON E.id = M.mid
Cross Self Join Employee table
SELECT E.Name as Employee, M.Name as Manager FROM Employee E CROSS JOIN Employee M
Replacing NULL value using CASE Statement
SELECT E.Name as Employee, CASE WHEN M.Name IS NULL THEN 'No Manager' ELSE M.Name END as Manager FROM Employee E LEFT JOIN Employee M ON E.mid = M.id
Replacing NULL value using COALESCE() function: COALESCE() function, returns the first NON NULL value.
SELECT E.Name as Employee, COALESCE(M.Name, 'No Manager') as Manager FROM Employee E LEFT JOIN Employee M ON E.mid = M.id
What does UNION do? What is the difference between UNION and UNION ALL?
UNION merges the contents of two structurally-compatible tables into a single combined table. The difference between UNION and UNION ALL is that UNION will omit duplicate records whereas UNION ALL will include duplicate records.
It is important to note that the performance of UNION ALL will typically be better than UNION, since UNION requires the server to do the additional work of removing any duplicates. So, in cases where is is certain that there will not be any duplicates, or where having duplicates is not a problem, use of UNION ALL would be recommended for performance reasons.
Select Id, Name, Email from tblIndiaCustomers UNION ALL Select Id, Name, Email from tblUKCustomers
For this reason, UNION ALL is much faster than UNION.
Note: For UNION and UNION ALL to work, the Number, Data types, and the order of the columns in the select statements should be same.
Difference between JOIN and UNION
JOINS and UNIONS are different things. However, this question is being asked very frequently now.
UNION combines the result-set of two or more select queries into a single result-set which includes all the rows from all the queries in the union,
where as JOINS, retrieve data from two or more tables based on logical relationships between the tables.
In short, UNION combines rows from 2 or more tables, where JOINS combine columns from 2 or more table.