In this video session we will learn about
1. Advanced or intelligent joins in SQL Server
2. Retrieve only the non matching rows from the left table
3. Retrieve only the non matching rows from the right table
4. Retrieve only the non matching rows from both the left and right table
Before watching this video, please watch Part 12 - Joins in SQL Server
Considers Employees (tblEmployee) and Departments (tblDepartment) tables
Employee Table (tblEmployee)
Departments Table (tblDepartment)
How to retrieve only the non matching rows from the left table. The output should be as shown below:
Query:
SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee E
LEFT JOIN tblDepartment D
ON E.DepartmentId = D.Id
WHERE D.Id IS NULL
How to retrieve only the non matching rows from the right table
Query:
SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee E
RIGHT JOIN tblDepartment D
ON E.DepartmentId = D.Id
WHERE E.DepartmentId IS NULL
How to retrieve only the non matching rows from both the left and right table. Matching rows should be eliminated.
Query:
SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee E
FULL JOIN tblDepartment D
ON E.DepartmentId = D.Id
WHERE E.DepartmentId IS NULL
OR D.Id IS NULL
1. Advanced or intelligent joins in SQL Server
2. Retrieve only the non matching rows from the left table
3. Retrieve only the non matching rows from the right table
4. Retrieve only the non matching rows from both the left and right table
Before watching this video, please watch Part 12 - Joins in SQL Server
Considers Employees (tblEmployee) and Departments (tblDepartment) tables
Employee Table (tblEmployee)
Departments Table (tblDepartment)
How to retrieve only the non matching rows from the left table. The output should be as shown below:
Query:
SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee E
LEFT JOIN tblDepartment D
ON E.DepartmentId = D.Id
WHERE D.Id IS NULL
How to retrieve only the non matching rows from the right table
Query:
SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee E
RIGHT JOIN tblDepartment D
ON E.DepartmentId = D.Id
WHERE E.DepartmentId IS NULL
How to retrieve only the non matching rows from both the left and right table. Matching rows should be eliminated.
Query:
SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee E
FULL JOIN tblDepartment D
ON E.DepartmentId = D.Id
WHERE E.DepartmentId IS NULL
OR D.Id IS NULL
hello sir ,
ReplyDeletei have a query i want to join two table one table party_table contain 4 column-- id |party_name | assign_user1 | assign_user2 | assign_user3 | assign_user4
01| abc11 | 100 |102 | null | 103
02| abc12 | 102 | 103 | 100 | null
and and second table contain party name and code like this
assigned_code | party_code
100 | abc16
102 | abc17
i want to join both of the table and get the all party name , from both of the table of and assigned user ..
is it possible to join one column from multiple column (check the favorable matching value )of another column and show in one table.
is it possible ..
@ALOK DEO, I think u cannot join the two tables you mentioned in the example because there is no common column in both the tables .When u have a common column in both tables u can get all the users using left join.
DeleteCORRECT ME IF I AM WRONG !!
Hi Venkat,
ReplyDeletecan you please explain how can we compare two tables in sql
Great tutorials, I have question though, I want to generate n number of rows in a table, how do I do it using sql (not using stored proc or function)
ReplyDeleten = 10 or 20 or ...
Query to generate 20 rows without using stored proc or function .. try this...
Deletedeclare @Start int
declare @End int
set @Start = 1
set @End = 20
while(@Start <= @End)
begin
Select convert(varchar,@Start) + ' Row' as Rows
Set @Start = @Start + 1
end
Hi Anonymous June 1,2006 at 11.20 ... the Answer for ur question is as follows....
ReplyDeleteExecute the below lines of code in sql .. u will 20 rows..
declare @Start int
declare @End int
set @Start = 1
set @End = 20
while(@Start <= @End)
begin
Select cast(@Start as varchar) + ' Row' as Rows
Set @Start = @Start + 1
end
You are genius in teaching...
ReplyDeleteFirst of all thanks to such wonderful explanatory videos.
ReplyDeleteOn that note, I have a question. Why these intelligent joins are used as this can be achieved without joining the tables when we actually do not need any information from the other table then just the filtering clause would do to achieve these results.
Your response is greatly appreciated.
Thanks
Raja
In finding the not matching from left/right in the Venkat`s illustration. Where clause =NULL is used. I suppose this is considering the two tables related by foreign- primary key. How can we solve this if there is no foreign key relationship ? Or can i use where table1.id != talbe2.id ?
ReplyDeleteHi Venkant, I want to know if table a has 10 rows and table b has 10 rows
ReplyDeleteif we perform a left join what is the minimum record count and maximum record count? Could you explain it with other type of joins as well?