Joins in Sqlserver
Joins are used to relate 2 or more tables and get the results. Create a primary key column will avoid duplicate rows in tables.
Suppose if we want to relate 2 tables to get the some result. We can create Two tables
For Ex:
Employee with fields as (EmpId primary key, empname)
EmpID Empname
101 JK
102 Kumar
103 Arul
104 Prakash
Tools with fields as (Product id Primary key, Product , Empid)
Product Id Product Empid
1001 Computer 102
1002 Jockey 103
1003 Hammer 103
Now this two can be related using the common key Employee id
Let us relate the 2 tables with query
Select Emp.Empname, Tool.Product from Employee emp, Tools Tool
Where Emp.Emplid=Tool.empid
Will give the below result.
Name Product
Kumar Computer
Arul Jockey
Arul Hammer
Inner Join
We can relate this using inner join like this
Syntax for inner join
SELECT field1, field2, field3
FROM first_table
INNER JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield
SELECT Employee. Empname, Tools.Product
FROM Employee
INNER JOIN Tools
ON Employee.Empid=Tools.Empid
The INNER JOIN returns all rows from both tables where there is a match. If there are rows in Employee that do not have matches in Tools, those rows will not be listed.
Name Product
Kumar Computer
Arul Jockey
Arul Hammer
Left Join
The LEFT JOIN returns all the rows from the first table, even if there are no matches in the second table. If there are rows in that do not have matches in second table, those rows also will be listed.
Syntax
SELECT field1, field2, field3
FROM first_table
LEFT JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield
The LEFT JOIN returns all the rows from the first table (Employee), even if there are no matches in the second table (Tools). If there are rows in that do not have matches in second table(Tools), those rows also will be listed.
Example
SELECT Employee. Empname, Tools.Product
FROM Employee
LEFT JOIN Tools
ON Employee.EmpID=Tools.EmpID.
Right Join
The RIGHT JOIN returns all the rows from the second table, even if there are no matches in the first table. If there had been any rows in second that did not have matches in First table, those rows also would have been listed
Syntax
SELECT field1, field2, field3
FROM first_table
RIGHT JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield
Example
SELECT Employees.Name, Tools.Empid
FROM Employee
RIGHT JOIN Tools
ON Employee.Empid=Tools.Empid
|
No responses found. Be the first to respond and make money from revenue sharing program.
|