Joins

A l l A b o u t J o i n s Step 1: Create Employee Table USE [Test] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Employee]( [EmployeeID] [int] NOT NULL, [EmployeeName] [nchar](50) COLLATE Latin1_General_CI_AS NOT NULL, [ContactID] [int] NULL, [ManagerID] [int] NULL, [MaritalStatus] [nchar](1) COLLATE Latin1_General_CI_AS NULL, [HireDate] [datetime] NULL, CONSTRAINT [PK_FK_Employee_EmployeeID] PRIMARY KEY CLUSTERED ( [EmployeeID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key for employee rows' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Employee', @level2type=N'COLUMN', @level2name=N'EmployeeID' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identifies the employee in the Contact table. Foreign key to Contact.ContactID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Employee', @level2type=N'COLUMN', @level2name=N'ContactID' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'M = Married S = Single' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Employee', @level2type=N'COLUMN', @level2name=N'MaritalStatus' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date on which the employee was hired' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Employee', @level2type=N'COLUMN', @level2name=N'HireDate' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Manager to whom the employee is assigned. Foreign key to Employee.EmployeeID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Employee', @level2type=N'CONSTRAINT', @level2name=N'PK_FK_Employee_EmployeeID' GO USE [Test] GO ALTER TABLE [dbo].[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_Contact] FOREIGN KEY([ContactID]) REFERENCES [dbo].[Contact] ([ContactID]) GO ALTER TABLE [dbo].[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_Manager] FOREIGN KEY([ManagerID]) REFERENCES [dbo].[Manager] ([ManagerID]) Step 2: Create EmployeeAddress Table USE [Test] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[EmployeeAddress]( [EmployeeID] [int] NOT NULL, [AddressID] [int] NOT NULL, [Address] [nchar](70) COLLATE Latin1_General_CI_AS NOT NULL, CONSTRAINT [PK_EmployeeAddress] PRIMARY KEY CLUSTERED ( [AddressID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Employee identification number. Foreign key to Employee.EmployeeID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'EmployeeAddress', @level2type=N'COLUMN', @level2name=N'EmployeeID' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Employee address identification number. Foreign key to Address.AddressID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'EmployeeAddress', @level2type=N'COLUMN', @level2name=N'AddressID' GO USE [Test] GO ALTER TABLE [dbo].[EmployeeAddress] WITH CHECK ADD CONSTRAINT [FK_EmployeeAddress_Employee] FOREIGN KEY([EmployeeID]) REFERENCES [dbo].[Employee] ([EmployeeID]) Step 3: Insert Some values for both Employee and Employeeaddress tables Step 4 Join query Workouts Inner Join Examples SELECT E.EmployeeID, E.EmployeeName,E.ContactID,A.AddressID, A.address FROM Employee E INNER JOIN Employeeaddress A ON A.EmployeeID = E.EmployeeID WHERE A.addressID > 10 -- or = 10 or <> ORDER BY E.Employeename, A.address Inner Natural Join Examples SELECT E.*, A.addressID, A.address FROM Employee E INNER JOIN Employeeaddress A ON A.EmployeeID = E.EmployeeID Inner Equi Join Example: SELECT * FROM Employee E INNER JOIN EmployeeAddress A ON A.EmployeeID = E.EmployeeID Who is belongs to which manager select e1.Managername 'Manager',e2.employeename 'Employee' from Manager e1 join employee e2 on e1.managerid=e2.ManagerID Left Outer Join Example SELECT E.Employeename, E.ManagerID, C.Contactname FROM employee E LEFT OUTER JOIN contact C ON E.contactID = C.contactid ORDER BY E.employeename Right Outer Join Example SELECT E.Employeename, E.ManagerID, C.Contactname FROM employee E Right OUTER JOIN contact C ON E.contactID = C.contactid ORDER BY E.employeename --ORDER BY E.employeename, A.AU_FNAME FULL Outer Join Example SELECT E.Employeename, E.ManagerID, C.Contactname FROM employee E FULL OUTER JOIN contact C ON E.contactID = C.contactid ORDER BY E.employeename --ORDER BY E.employeename, A.AU_FNAME CROSS Join Example SELECT Employeename, EmployeeID, ContactName FROM Employee CROSS JOIN Contact ORDER BY Employeename LEFT OUTER JOIN that even returns the employees without managers SELECT t1.EmployeeName [Employee], COALESCE(t2.EmployeeName, 'No manager') [Manager] FROM Employee t1 LEFT OUTER JOIN Employee t2 ON t1.ManagerID = t2.EmployeeID