[CS] SQL Exercise Day-80

SQL JOIN

Join is used to combine rows from two or more tables, based on a related column between them.

ex) Orders table

ex) Customers table

CustomerId Column in the Orders table refers to the CustomerId in the Customers table. The relationship between the two tables above is the CustomerId Column.

Then we can create SQL statement use by INNER JOIN. that selects records that have matching values in both tables.

ex) INNER JOIN

SELECT Orders.OrderId, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId;

ex) Result

Different Types of SQL JOINs

• INNER JOIN: Returns records that have matching values in both tables.

• LEFT JOIN: Returns all records from the left table, and the matched records from the right table.

• RIGHT JOIN: Opposite with LEFT JOIN

• FULL JOIN: Returns all records when there is a match in either left or right table.

INNER JOIN

INNER JOIN selects records that have matching values in both tables.

SELECT column_name
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

ex) Orders table

ex) Customers

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

LEFT JOIN

returns all records from the left table (table1), and the matching records from the right table (table2)

SELECT column_name
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

ex) Customers table

ex) Orders table

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

GROUP BY

statement groups rows that have the same values into summary rows, like "find the number of customers in each country".

SELECT column_name
FROM table_name
WHERE condition
GROUP BY column_name
ORDER BY column_name

ex) Customers table

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;

GROUP BY With JOIN

ex) Orders table

ex) Shippers table

SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders 
FROM Orders 
LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
GROUP BY ShipperName;

SELECT INTO

statement copies data from one table into a new table.

ex) Copy all columns into a new table

SELECT *
INTO newtable
FROM oldtable
WHERE condition;

ex) Copy only some columns into a new table

SELECT column1, column2, column3
INTO newtable
FROM oldtable
WHERE condition;

ex) Copies only specific into a new table

SELECT *
INTO CustomersGermany
FROM Customers
WHERE Country = "Germany";

INSERT INTO SELECT

statement copies data from one table and inserts it into another table.

ex) Copy all columns from one table to another table

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

ex) Copy only some columns from one table into another table.

INSERT INTO table2
SELECT column1, column2, column3
FROM table1
WHERE condition;

Following SQL statement copies Suppliers into Customers

INSERT INTO Customers
SELECT SupplierName, City, Country FROM Suppliers;