" Wingardian Leviosa "

[3] SQL Notes – Subqueries and Joins

Subquery

Be sure to use indenting.

SELECT CustomerID,
CompanyName,
Region

FROM Customers

WHERE cutomerID IN (SELECT customerID
                   FROM Orders
                   WHERE Freight > 100)

The power of subqueries

  • Subqueries are powerful tools
  • Not always the best option due to performance

 

Aliases

two ways:

SELECT vendor_name,
product_name,
product_price

FROM Vendors AS v, Products AS p
WHERE v.vendor_id = p. vendor_id

 

SELECT vendor_name,
product_name,
product_price

FROM Vendors v, Products p
WHERE v.vendor_id = p. vendor_id

 

 

JOIN

Benefits of breaking data into tables

  • Efficient storage
  • Easier manipulation
  • Greater scalability
  • Logically models a process
  • Tables are related through common values (keys)

 

Why JOIN?

  • Associate correct records from each table on the fly.
  • Allows data retrieval from multiple tables in one query.
  • Joins are not physical, they persist data for the duration of the query execution.

 

Clause

Cartesian (Cross) Joins

  • Not frequently used.
  • Computationally taxing.
  • Will return products with the incorrect vendor or no vendor at all.

 

table1: m rows table2: n rows

table1 CROSS JOIN table2: m*n rows

 

SELECT product_name,
unit_price,
company_name

FROM suppliers CROSS JOIN products

Computationally taxing

 

INNER Joins

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

 

img

 

SELECT suppliers.CompanyName,
ProductName,
UnitPrice

FROM suppliers INNER JOIN products
ON Suppliers.supplierid = Products.supplierid

 

Join multiple tables is ok

SELECT o.OrderID,
c.CompanyName,
e.LastNmae

FROM ((Orders o INNER JOIN Customers c ON o.CustomerID = c.CustomerID)
INNER JOIN Employees e ON o.EmployeeID = e.EmployeeID);

 

 

Self Joins

 

SELECT column_names

FROM Table1 T1, Table2 T2

WHERE conditions;

Examples:

SELECT A.CustomerName AS CustomerName1
,B.CustomerName AS CustomerName2
,A.City
FROM Customers A, Customers B
WHERE A.CustomerID = B.CustomerID
AND A.City = B.City
ORDER BY A.City;

 

Left Joins

Return all records from the left table, and the matched records from the right table.

The result is NULL from the right side, if there is no match.

img

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons LEFT JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName

 

Right Joins

Return all records from the right table, and the matched records from the left table.

The result is NULL from the left side, if there is no match.

img

 

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons RIGHT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

 

 

Full Outer Joins

Return all records when there is a match in either left table or right table records.

Select all customers and orders:

img

 

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons 
FULL OUTER JOIN Orders
ON Persons.Id_P = Orders.Id_P

 

Left Join Excluding INNER JOIN

img

 

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons 
LEFT JOIN Orders
ON Persons.Id_P = Orders.Id_P
WHERE Order.Id_P IS NULL

DO NOT replace IS NULL with = NULL

 

Right Join Excluding INNER JOIN

img

 

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons 
RIGHT JOIN Orders
ON Persons.Id_P = Orders.Id_P
WHERE Persons.Id_P IS NULL

 

Full Outter Join Excluding INNER JOIN

 

img

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons 
FULL OUTER JOIN Orders
ON Persons.Id_P = Orders.Id_P
WHERE Persons.Id_P IS NULL 
OR Orders.Id_P

 

 

 

 

Union

  • The UNION operator is used to combine the result-set of two or more SELECT statements.
  • Each SELECT statement with UNION must have the same number of columns.
  • Columns must have similar data types.
  • The columns in each SELECT statement must be in the same order.
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

<

ul>

  • UNION selects only distinct values.
  • Use UNION ALL to also select duplicate values!
  •  

     

    Reference:https://shipengfei92.github.io/2018/02/05/SQL-for-data-science/

    YOU MIGHT ALSO LIKE

    0 0 vote
    Article Rating
    Subscribe
    提醒
    guest
    0 评论
    Inline Feedbacks
    View all comments
    0
    Would love your thoughts, please comment.x
    ()
    x