1. SQL Tutorial (6) SQL Aliases, Joins, UNION, GROUP BY, HAVING

SQL Aliases

• used to give a table or a column in a table a temporary name
• used to make column names more readable
• only exists for the duration of that query
• is created with the AS keyword
• requires double quotation marks or square brackets if the alias name contains spaces
• can be useful when
  • there are more than one table involved in a query
  • functions are used in the query
  • column names are big or not very readable
  • two or more columns are combined together

Alias Column Syntax

SELECT column_name AS alias_name
FROM table_name;

Alias Table Syntax

SELECT column_name(s)
FROM table_name AS alias_name;

Examples

1. Alias for columns: creates 2 aliases

SELECT CustomerName AS Customer, ContactName AS [Contact Person]
FROM Customers;

2. Alias for multiple columns: creates an alias that combines 4 columns

SELECT CustomerName, CONCAT(Address,', ',PostalCode,', ',City,
', ',Country) AS Address
FROM Customers;

3. Alias for tables

SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerName='Around the Horn' AND c.CustomerID=o.CUstomerID;

---

SQL Joins

JOIN clause: used to combine rows from two or more tables, based on a related column b/w them

• (INNER) JOIN: returns records that have matching values in both tables
• LEFT (OUTER) JOIN: returns all records from the left table, and the matched records from the right table
• RIGHT (OUTER) JOIN: returns all records from the right table, and the matched records from the left table
• FULL (OUTER) JOIN: returns all records when there is a match in either left or right table
  

---

SQL INNER JOIN Keyword

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

Syntax

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Example

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

Join three tables

SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM ((Orders 
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);

---

SQL LEFT JOIN Keyword

LEFT JOIN: returns all records from the table1 and the matching records from the right table

Syntax

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

---

SQL RIGHT JOIN Keyword

RIGHT JOIN: returns all records from the table2 and the matching records from table1

Syntax

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

---

SQL FULL OUTER JOIN Keyword

FULL OUTER JOIN: returns all records when there is a match in table1 or table2 records

Syntax

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

---

SQL Self Join

Self Join: a regular join but the table is joined with itself

Syntax

• T1 and T2 are different table aliases for the same table

SELECT column_name(s)
FROM table1 T1, table1 T2 #aliases done without AS example
WHERE condition;

Example

SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID #not equal
AND A.City = B.City
ORDER BY A.City;

---

SQL UNION Operator

UNION operator: used to combine the result-set of two or more SELECT statements

• every SELECT statement within UNION must have the same number of columns
• columns must have similar data types
• columns in every SELECT statement must also be in the same order

UNION Syntax: selects only distinct values by default

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

UNION ALL Syntax: allows duplicate values

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

---

SQL GROUP BY Statement

GROUP BY: groups rows that have the same values into summary rows

• (ex) “find the number of customers in each country”
• often used w/ aggregate functionsCOUNT(),  MAX(),  MIN(),  SUM(),  AVG()) to group the result-set by one or more columns
  Syntax

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

SQL HAVING Clause

HAVING: replacement for WHERE keyword when you need to use aggregate functions
Syntax

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

Example

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

---

This post is based on W3School's articles about SQL.