Created
August 3, 2017 10:20
-
-
Save henrytran9x/c4feb35eda410e09e9d760bb7d3c5c03 to your computer and use it in GitHub Desktop.
Demo build Schema and Query about Join !
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /*** CREATE table Departments **/ | |
| CREATE TABLE Departments( | |
| DepartmentID INT IDENTITY(1,1) PRIMARY KEY, | |
| DepartmentName varchar(500) | |
| ); | |
| /* Insert record into table Departments */ | |
| INSERT INTO Departments (DepartmentName) VALUES | |
| ('IT'),('HR'),('Payroll'),('Admin'); | |
| /** CREATE TABLE Employees **/ | |
| CREATE TABLE Employees( | |
| EmployeeID INT IDENTITY(1,1) PRIMARY KEY, | |
| EmployeeName varchar(500), | |
| DepartmentID INT DEFAULT NULL | |
| ); | |
| /** Insert record into table Employees **/ | |
| INSERT INTO Employees (EmployeeName,DepartmentID) VALUES | |
| ('Mark',1),('John',1),('Mike',1),('Mary',2),('Stacy',3),('Pam',null); | |
| /** LEFT JOIN **/ | |
| SELECT em.EmployeeName,de.DepartmentName FROM Employees AS em | |
| LEFT JOIN Departments AS de ON de.DepartmentID = em.DepartmentID; | |
| /** RIGHT JOIN **/ | |
| SELECT em.EmployeeName,de.DepartmentName FROM Employees AS em | |
| RIGHT JOIN Departments AS de ON de.DepartmentID = em.DepartmentID; | |
| /** INNER JOIN **/ | |
| SELECT em.EmployeeName,de.DepartmentName FROM Employees AS em | |
| INNER JOIN Departments AS de ON de.DepartmentID = em.DepartmentID; | |
| /** FULL OUTER JOIN **/ | |
| SELECT em.EmployeeName,de.DepartmentName FROM Employees AS em | |
| FULL OUTER JOIN Departments AS de ON de.DepartmentID = em.DepartmentID; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment