INNER JOIN:
An INNER JOIN returns only the rows that have matching values in both tables based on the
specified condition in the ON clause.
Syntax: SELECT columns
FROM table1 INNER JOIN table2 ON table1.column = table2.column
LEFT JOIN(or LEFT OUTER JOIN):
A LEFT JOIN returns all rows from the left table(table1), and the
matched rows from the right table(table2).If there are no matching rows, NULL
values are returned for the columns from the right table.
Syntax: SELECT columns
FROM table1 LEFT JOIN table2 ON table1.column = table2.column
RIGHT JOIN(or RIGHT OUTER JOIN):
A RIGHT JOIN returns all rows from the right table(table2), and
the matched rows from the left table(table1).If there are no matching rows,
NULL values are returned for the columns from the left table.
Syntax: SELECT columns
FROM table1 RIGHT JOIN table2 ON table1.column = table2.column
FULL JOIN(or FULL OUTER JOIN):
A FULL JOIN returns all rows when there is a match in either the left
table(table1) or the right table(table2).If there is no match, NULL values are
returned for the columns from the table without a match.
.png)
SELECT Employees.EmployeeID, Employees.EmployeeName, Departments.DepartmentName
FROM Employees
INNER JOIN
Departments ON Employees.DepartmentID = Departments.DepartmentID;
SELECT Employees.EmployeeID, Employees.EmployeeName, Departments.DepartmentName
FROM Employees
LEFT JOIN
Departments ON Employees.DepartmentID = Departments.DepartmentID;
SELECT *
FROM Table1
CROSS JOIN Table2;
SELECT *
FROM Table1
INNER JOIN Table2 ON Table1.ID = Table2.ID;
SELECT *
FROM Table1
LEFT JOIN Table2 ON Table1.ID = Table2.ID;
SELECT *
FROM Table1
RIGHT JOIN Table2 ON Table1.ID = Table2.ID;
SELECT *
FROM Table1
FULL JOIN Table2 ON Table1.ID = Table2.ID;
SELECT Employees.EmployeeID, Employees.EmployeeName, Departments.DepartmentName, Projects.ProjectName
FROM Employees
LEFT JOIN
Departments ON Employees.DepartmentID = Departments.DepartmentID
INNER JOIN Projects
ON Employees.ProjectID = Projects.ProjectID;
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN
Customers ON Orders.CustomerID = Customers.CustomerID;
CREATE TABLE
EmployeeHierarchy (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(100),
SupervisorID INT
);
INSERT INTO
EmployeeHierarchy (EmployeeID, EmployeeName, SupervisorID)
VALUES
(1, 'John', NULL),
(2, 'Alice', 1),
(3, 'Bob', 1),
(4, 'Charlie', 2),
(5, 'Diana', 2);
SELECT e.EmployeeName AS Employee, s.EmployeeName AS Supervisor
FROM EmployeeHierarchy e
LEFT JOIN
EmployeeHierarchy s ON e.SupervisorID = s.EmployeeID;
CREATE TABLE
Customers (
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(100)
);
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
INSERT INTO
Customers (CustomerID, CustomerName)
VALUES
(1, 'A EE'),
(2, 'B SS'),
(3, 'C DD');
INSERT INTO Orders (OrderID, CustomerID, OrderDate)
VALUES
(101, 1, '2023-01-10'),
(102, 2, '2023-02-15'),
(103, 3, '2023-03-20');
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
RIGHT JOIN
Customers ON Orders.CustomerID = Customers.CustomerID;
SELECT *
FROM Employees
CROSS JOIN
Departments;
SELECT
EmployeeID,
EmployeeName,
ISNULL(Salary, 0) AS Salary, -- Replace NULL
with 0 for Salary
ISNULL(Department, 'Unknown') AS Department -- Replace NULL with 'Unknown' for
Department
FROM Employee;
SELECT
EmployeeID,
EmployeeName,
Salary,
CASE
WHEN Salary IS NULL THEN 'Salary not available'
WHEN Salary < 50000 THEN 'Below Average'
WHEN Salary >= 50000 AND Salary < 70000 THEN 'Average'
ELSE 'Above Average'
END AS SalaryStatus
FROM Employee;
SELECT
EmployeeID,
EmployeeName,
COALESCE(Salary, 0) AS Salary, -- Replace NULL
with 0 for Salary
COALESCE(Department, 'Unknown') AS Department -- Replace NULL with 'Unknown' for
Department
FROM Employee;
SELECT
OrderID,
CustomerName,
COALESCE(ShippingAddress, BillingAddress, AlternativeAddress, 'Address not
available') AS Address,
OrderDate,
ShippedDate
FROM Orders;
SELECT ID, Name FROM Table1
UNION ALL
SELECT ID, Name FROM Table2;
SELECT ID, Name FROM Table1
UNION
SELECT ID, Name FROM Table2;