SQL Tutorial

1. Get Columns from Table

The SELECT statement is used to select data from a database.

The data returned is stored in a result table, called the result-set.

SELECT Syntax:

2
1
SELECT column_1, column_2, ...
2
FROM table_name1;

Here, column1, column2, ... are the field names of the table you want to select data from.

If you want to select all the fields available in the table, use the following syntax:

2
1
# selects all the columns from the "Customers" table:
2
SELECT * FROM Customers;

1.1 Get Distinct Columns from one Table

The SELECT DISTINCT statement is used to return only distinct (different) values.

Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

SELECT DISTINCT Syntax:

2
1
SELECT DISTINCT column1, column2, ...
2
FROM table_name1;

The following SQL statement lists the number of different (distinct) customer countries:

1
1
SELECT COUNT(DISTINCT Country) FROM Customers;

Here is the workaround for MS Access:

2
1
SELECT Count(*) AS DistinctCountries
2
FROM (SELECT DISTINCT Country FROM Customers);

2. Where Clause in Table

The WHERE clause is used to filter records.

It is used to extract only those records that fulfill a specified condition.

WHERE Syntax

3
1
SELECT column1, column2, ...
2
FROM table_name1
3
WHERE condition1;

Note: The WHERE clause is not only used in SELECT statements, it is also used in UPDATE, DELETE, etc.!

Example

• The following SQL statement selects all the customers from the country "Mexico", in the "Customers" table:

2
1
SELECT * FROM Customers
2
WHERE Country='Mexico';

2.1. And, Or, Not

AND, OR and NOT Operators

The WHERE clause can be combined with AND, OR, and NOT operators.

The AND and OR operators are used to filter records based on more than one condition:

• The AND operator displays a record if all the conditions separated by AND are TRUE.
• The OR operator displays a record if any of the conditions separated by OR is TRUE.

The NOT operator displays a record if the condition(s) is NOT TRUE.

Example

• Use the NOT keyword to select all records where City is NOT "Berlin".

2
1
SELECT * FROM Customers
2
WHERE NOT City = 'Berlin';

• Numeric fields should not be enclosed in quotes:

2
1
SELECT * FROM Customers
2
WHERE CustomerID=1;

• Select all records where the City column has the value 'Berlin' and the PostalCode column has the value 12209.

3
1
SELEC * FROM Customers
2
WHERE City = 'Berlin'
3
AND PostalCode = 12209;

• Select all records where the City column has the value 'Berlin' or 'London'.

3
1
SELECT * FROM Customers
2
WHERE City = 'Berlin'
3
OR City = 'London';

Operators in The WHERE Clause

The following operators can be used in the WHERE clause:

2.2 Is Null/ Is Not Null

A field with a NULL value is a field with no value.

If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.

IS NULL Syntax

3
1
SELECT column_names
2
FROM table_name1
3
WHERE column_name IS NULL;

IS NOT NULL Syntax

3
1
SELECT column_names
2
FROM table_name1
3
WHERE column_name IS NOT NULL;

Example

The IS NULL operator is used to test for empty values (NULL values).

• The following SQL lists all customers with a NULL value in the "Address" field:

3
1
SELECT CustomerName, ContactName, Address
2
FROM Customers
3
WHERE Address IS NULL;

The IS NOT NULL operator is used to test for non-empty values (NOT NULL values).

• The following SQL lists all customers with a value in the "Address" field:

3
1
SELECT CustomerName, ContactName, Address
2
FROM Customers
3
WHERE Address IS NOT NULL;

2.3 Like

LIKE Operator:

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

There are two wildcards often used in conjunction with the LIKE operator:

• The percent sign (%) represents zero, one, or multiple characters
• The underscore sign (_) represents one, single character

Note: MS Access uses an asterisk (*) instead of the percent sign (%), and a question mark (?) instead of the underscore (_).

LIKE Syntax

3
1
SELECT column1, column2, ...
2
FROM table_name1
3
WHERE columnN LIKE pattern;

Tip: You can also combine any number of conditions using AND or OR operators.

Example

• The following SQL statement selects all customers with a CustomerName starting with "a":

2
1
SELECT * FROM Customers
2
WHERE CustomerName LIKE 'a%';

• The following SQL statement selects all customers with a CustomerName that does NOT start with "a":

2
1
SELECT * FROM Customers
2
WHERE CustomerName NOT LIKE 'a%';

• Select all records where the value of the City column contains the letter "a".

2
1
SELECT * FROM Customers
2
WHERE City LIKE '%a%';

• Select all records where the value of the City column starts with letter "a" and ends with the letter "b".

2
1
SELECT * FROM Customers
2
WHERE City LIKE 'a%b';

• Select all records where the value of the City column does NOT start with the letter "a".

2
1
SELECT * FROM Customers
2
WHERE City NOT LIKE 'a%';

2.4 Wildcards

Wildcard Characters:

A wildcard character is used to substitute one or more characters in a string.

Wildcard characters are used with the LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

Wildcard Characters in MS Access

Wildcard Characters in SQL Server

Example

Here are some examples showing different LIKE operators with '%' and '_' wildcards:

• Select all records where the second letter of the City is an "a".

2
1
SELECT * FROM Customers
2
WHERE City LIKE '_a%';

• Select all records where the first letter of the City is an "a" or a "c" or an "s".

2
1
SELECT * FROM Customers
2
WHERE City LIKE '[acs]%';

• Select all records where the first letter of the City starts with anything from an "a" to an "f".

2
1
SELECT * FROM Customers
2
WHERE City LIKE '[a-f]%';

• Select all records where the first letter of the City is NOT an "a" or a "c" or an "f".

2
1
SELECT * FROM Customers
2
WHERE City LIKE '[!acf]%';

2.5 In (a shorthand for multiple OR conditions)

IN Operator:

The IN operator allows you to specify multiple values in a WHERE clause.

The IN operator is a shorthand for multiple OR conditions.

IN Syntax

3
1
SELECT column_name(s)
2
FROM table_name1
3
WHERE column_name IN (value1, value2, ...);

or:

3
1
SELECT column_name(s)
2
FROM table_name1
3
WHERE column_name IN (SELECT STATEMENT);

Example

• Use the IN operator to select all the records where Country is either "Norway" or "France".

2
1
SELECT * FROM Customers
2
WHERE Country IN ('Norway','France');

• Use the IN operator to select all the records where Country is NOT "Norway" and NOT "France".

2
1
SELECT * FROM Customers
2
WHERE Country NOT IN ('Norway', 'France');

• The following SQL statement selects all customers that are from the same countries as the suppliers:

2
1
SELECT * FROM Customers
2
WHERE Country IN (SELECT Country FROM Suppliers);

2.6 Between And

BETWEEN Operator:

The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.

The BETWEEN operator is inclusive: begin and end values are included.

BETWEEN Syntax

3
1
SELECT column_name(s)
2
FROM table_name1
3
WHERE column_name BETWEEN value1 AND value2;

Example

• The following SQL statement selects all products with a price between 10 and 20:

2
1
SELECT * FROM Products
2
WHERE Price BETWEEN 10 AND 20;

• To display the products outside the range of the previous example, use NOT BETWEEN:

2
1
SELECT * FROM Products
2
WHERE Price NOT BETWEEN 10 AND 20;

• The following SQL statement selects all products with a price between 10 and 20. In addition; do not show products with a CategoryID of 1,2, or 3:

3
1
SELECT * FROM Products
2
WHERE Price BETWEEN 10 AND 20
3
AND CategoryID NOT IN (1,2,3);

• The following SQL statement selects all products with a ProductName between Carnarvon Tigers and Mozzarella di Giovanni:

3
1
SELECT * FROM Products
2
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
3
ORDER BY ProductName;

• The following SQL statement selects all orders with an OrderDate between '01-July-1996' and '31-July-1996':

2
1
SELECT * FROM Orders
2
WHERE OrderDate BETWEEN #07/01/1996# AND #07/31/1996#;

or:

2
1
SELECT * FROM Orders
2
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';

2.7 Exists

EXISTS Operator:

The EXISTS operator is used to test for the existence of any record in a subquery.

The EXISTS operator returns TRUE if the subquery returns one or more records.

EXISTS Syntax

xxxxxxxxxx
1
SELECT column_name(s)
2
FROM table_name1
3
WHERE EXISTS
4
(SELECT column_name FROM table_name1 WHERE condition1);

Examples

• The following SQL statement returns TRUE and lists the suppliers with a product price less than 20:

3
1
SELECT SupplierName
2
FROM Suppliers
3
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);

• The following SQL statement returns TRUE and lists the suppliers with a product price equal to 22:

3
1
SELECT SupplierName
2
FROM Suppliers
3
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price = 22);

3. Having Clause in Table

HAVING Clause:

The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.

HAVING Syntax

xxxxxxxxxx
6
1
SELECT column_name(s)
2
FROM table_name1
3
WHERE condition1
4
GROUP BY column_name(s)
5
HAVING condition1
6
ORDER BY column_name(s);

Example

• The following SQL statement lists the number of customers in each country. Only include countries with more than 5 customers:

4
1
SELECT COUNT(CustomerID), Country
2
FROM Customers
3
GROUP BY Country
4
HAVING COUNT(CustomerID) > 5;

• The following SQL statement lists the number of customers in each country, sorted high to low (Only include countries with more than 5 customers):

5
1
SELECT COUNT(CustomerID), Country
2
FROM Customers
3
GROUP BY Country
4
HAVING COUNT(CustomerID) > 5
5
ORDER BY COUNT(CustomerID) DESC;

• The following SQL statement lists the employees that have registered more than 10 orders:

5
1
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
2
FROM (Orders
3
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID)
4
GROUP BY LastName
5
HAVING COUNT(Orders.OrderID) > 10;

• The following SQL statement lists if the employees "Davolio" or "Fuller" have registered more than 25 orders:

6
1
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
2
FROM Orders
3
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
4
WHERE LastName = 'Davolio' OR LastName = 'Fuller'
5
GROUP BY LastName
6
HAVING COUNT(Orders.OrderID) > 25;

4. Order By in Table

ORDER BY Keyword:

The ORDER BY keyword is used to sort the result-set in ascending or descending order.

The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

ORDER BY Syntax

3
1
SELECT column1, column2, ...
2
FROM table_name1
3
ORDER BY column1, column2, ... ASC|DESC;

Example

• The following SQL statement selects all customers from the "Customers" table, sorted by the "Country" column:

2
1
SELECT * FROM Customers
2
ORDER BY Country;

• The following SQL statement selects all customers from the "Customers" table, sorted DESCENDING by the "Country" column:

2
1
SELECT * FROM Customers
2
ORDER BY Country DESC;

• The following SQL statement selects all customers from the "Customers" table, sorted by the "Country" and the "CustomerName" column. This means that it orders by Country, but if some rows have the same Country, it orders them by CustomerName:

2
1
SELECT * FROM Customers
2
ORDER BY Country, CustomerName;

• The following SQL statement selects all customers from the "Customers" table, sorted ascending by the "Country" and descending by the "CustomerName" column:

2
1
SELECT * FROM Customers
2
ORDER BY Country ASC, CustomerName DESC;

5. Insert Into-Values for Table

INSERT INTO Statement

The INSERT INTO statement is used to insert new records in a table.

INSERT INTO Syntax

It is possible to write the INSERT INTO statement in two ways:

• Specify both the column names and the values to be inserted:

2
1
INSERT INTO table_name1 (column1, column2, column3, ...)
2
VALUES (value1, value2, value3, ...);

• If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. Here, the INSERT INTO syntax would be as follows:

2
1
INSERT INTO table_name1
2
VALUES (value1, value2, value3, ...);

Example

• The following SQL statement inserts a new record in the "Customers" table:

2
1
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
2
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

• It is also possible to only insert data in specific columns.

  The following SQL statement will insert a new record, but only insert data in the "CustomerName", "City", and "Country" columns (CustomerID will be updated automatically):

2
1
INSERT INTO Customers (CustomerName, City, Country)
2
VALUES ('Cardinal', 'Stavanger', 'Norway');

6. Aliases

SQL aliases are used to give a table, or a column in a table, a temporary name.

Aliases are often used to make column names more readable.

An alias only exists for the duration of that query.

An alias is created with the AS keyword.

Alias Column Syntax

xxxxxxxxxx
1
SELECT column_name AS alias_name
2
FROM table_name1;

Alias Table Syntax

xxxxxxxxxx
1
SELECT column_name(s)
2
FROM table_name1 AS alias_name;

Columns Example

• The following SQL statement creates two aliases, one for the CustomerID column and one for the CustomerName column:

2
1
SELECT CustomerID AS ID, CustomerName AS Customer
2
FROM Customers;

• The following SQL statement creates two aliases, one for the CustomerName column and one for the ContactName column.

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

Note: It requires double quotation marks or square brackets if the alias name contains spaces.

• The following SQL statement creates an alias named "Address" that combine four columns (Address, PostalCode, City and Country):

2
1
SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City + ', ' + Country AS Address
2
FROM Customers;

Note: To get the SQL statement above to work in MySQL use the following:

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

Note: To get the SQL statement above to work in Oracle use the following:

2
1
SELECT CustomerName, (Address || ', ' || PostalCode || ' ' || City || ', ' || Country) AS Address
2
FROM Customers;

Tables Example

• The following SQL statement selects all the orders from the customer with CustomerID=4 (Around the Horn). We use the "Customers" and "Orders" tables, and give them the table aliases of "c" and "o" respectively (Here we use aliases to make the SQL shorter):

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

• The following SQL statement is the same as above, but without aliases:

xxxxxxxxxx
3
1
SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName
2
FROM Customers, Orders
3
WHERE Customers.CustomerName='Around the Horn' AND Customers.CustomerID=Orders.CustomerID;

7. Update-Set for Table

UPDATE Statement:

The UPDATE statement is used to modify the existing records in a table.

Note: Be careful when updating records in a table! Notice the WHERE clause in the UPDATE statement. The WHERE clause specifies which record(s) that should be updated. If you omit the WHERE clause, all records in the table will be updated!

UPDATE Syntax

3
1
UPDATE table_name1
2
SET column1 = value1, column2 = value2, ...
3
WHERE condition1;

Example

• The following SQL statement updates the first customer (CustomerID = 1) with a new contact person and a new city.

3
1
UPDATE Customers
2
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
3
WHERE CustomerID = 1;

• UPDATE Multiple Records

It is the WHERE clause that determines how many records will be updated.

The following SQL statement will update the ContactName to "Juan" for all records where country is "Mexico":

3
1
UPDATE Customers
2
SET ContactName='Juan'
3
WHERE Country='Mexico';

Note: Be careful when updating records. If you omit the WHERE clause, ALL records will be updated!

• Update the City column of all records in the Customers table.

2
1
UPDATE Customers
2
SET City = 'Oslo';

8. Delete from Table

DELETE Statement:

The DELETE statement is used to delete existing records in a table.

DELETE Syntax

1
1
DELETE FROM table_name1 WHERE condition1;

Example

• The following SQL statement deletes the customer "Alfreds Futterkiste" from the "Customers" table:

1
1
DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';

Note: Be careful when deleting records in a table! Notice the WHERE clause in the DELETE statement. The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted!

• Delete All Records

  It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:

1
1
DELETE FROM Customers;

9. Functions: Top, Min/Max, Count, Avg, Sum from Table

9.1 Top

SELECT TOP Clause:

The SELECT TOP clause is used to specify the number of records to return.

The SELECT TOP clause is useful on large tables with thousands of records. Returning a large number of records can impact performance.

Note: Not all database systems support the SELECT TOP clause. MySQL supports the LIMIT clause to select a limited number of records, while Oracle uses FETCH FIRST *n* ROWS ONLY and ROWNUM.

SQL Server / MS Access Syntax:

3
1
SELECT TOP number|percent column_name(s)
2
FROM table_name1
3
WHERE condition1;

MySQL Syntax:

4
1
SELECT column_name(s)
2
FROM table_name1
3
WHERE condition1
4
LIMIT number;

Oracle 12 Syntax:

4
1
SELECT column_name(s)
2
FROM table_name1
3
ORDER BY column_name(s)
4
FETCH FIRST number ROWS ONLY;

Example

• The following SQL statement selects the first three records from the "Customers" table (for SQL Server/MS Access):

1
1
SELECT TOP 3 * FROM Customers;

• The following SQL statement shows the equivalent example for MySQL:

2
1
SELECT * FROM Customers
2
LIMIT 3;

• The following SQL statement shows the equivalent example for Oracle:

2
1
SELECT * FROM Customers
2
FETCH FIRST 3 ROWS ONLY;

• The following SQL statement selects the first 50% of the records from the "Customers" table (for SQL Server/MS Access):

1
1
SELECT TOP 50 PERCENT * FROM Customers;

• The following SQL statement shows the equivalent example for Oracle:

2
1
SELECT * FROM Customers
2
FETCH FIRST 50 PERCENT ROWS ONLY;

9.2 Min/Max

MIN() and MAX() Functions:

The MIN() function returns the smallest value of the selected column.

The MAX() function returns the largest value of the selected column.

MIN() Syntax

3
1
SELECT MIN(column_name)
2
FROM table_name1
3
WHERE condition1;

MAX() Syntax

3
1
SELECT MAX(column_name)
2
FROM table_name1
3
WHERE condition1;

Example

• The following SQL statement finds the price of the cheapest product:

2
1
SELECT MIN(Price) AS SmallestPrice
2
FROM Products;

• The following SQL statement finds the price of the most expensive product:

2
1
SELECT MAX(Price) AS LargestPrice
2
FROM Products;

9.3 COUNT, AVG, SUM

• The COUNT() function returns the number of rows that matches a specified criterion.

COUNT() Syntax

3
1
SELECT COUNT(column_name)
2
FROM table_name1
3
WHERE condition1;

• The AVG() function returns the average value of a numeric column.

AVG() Syntax

3
1
SELECT AVG(column_name)
2
FROM table_name
3
WHERE condition;

• The SUM() function returns the total sum of a numeric column.

SUM() Syntax

3
1
SELECT SUM(column_name)
2
FROM table_name
3
WHERE condition;

Example

• The following SQL statement finds the number of products:

2
1
SELECT COUNT(ProductID)
2
FROM Products;

Note: NULL values are not counted.

• The following SQL statement finds the average price of all products:

2
1
SELECT AVG(Price)
2
FROM Products;

Note: NULL values are ignored.

• The following SQL statement finds the sum of the "Quantity" fields in the "OrderDetails" table:

2
1
SELECT SUM(Quantity)
2
FROM OrderDetails;

Note: NULL values are ignored.

10. Join Two Tables

JOIN:

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

10.1 Inner Join On

INNER JOIN Keyword:

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

INNER JOIN Syntax

4
1
SELECT column_name(s)
2
FROM table1
3
INNER JOIN table2
4
ON table1.column_name = table2.column_name;

Example

• The following SQL statement selects all orders with customer information:

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

Note: The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are records in the "Orders" table that do not have matches in "Customers", these orders will not be shown!

• The following SQL statement selects all orders with customer and shipper information:

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

10.2 Left Join On

LEFT JOIN Keyword:

The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side, if there is no match.

LEFT JOIN Syntax

4
1
SELECT column_name(s)
2
FROM table1
3
LEFT JOIN table2
4
ON table1.column_name = table2.column_name;

Note: In some databases LEFT JOIN is called LEFT OUTER JOIN.

Example

• The following SQL statement will select all customers, and any orders they might have:

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

10.3 Right Join On

RIGHT JOIN Keyword:

The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records from the left table (table1). The result is 0 records from the left side, if there is no match.

RIGHT JOIN Syntax

4
1
SELECT column_name(s)
2
FROM table1
3
RIGHT JOIN table2
4
ON table1.column_name = table2.column_name;

Note: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

Example

• The following SQL statement will return all employees, and any orders they might have placed:

4
1
SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
2
FROM Orders
3
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
4
ORDER BY Orders.OrderID;

10.4 Full Outer Join On

FULL OUTER JOIN Keyword:

The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records.

Tip: FULL OUTER JOIN and FULL JOIN are the same.

FULL OUTER JOIN Syntax

5
1
SELECT column_name(s)
2
FROM table1
3
FULL OUTER JOIN table2
4
ON table1.column_name = table2.column_name
5
WHERE condition;

Note: FULL OUTER JOIN can potentially return very large result-sets!

Example

• The following SQL statement selects all customers, and all orders:

4
1
SELECT Customers.CustomerName, Orders.OrderID
2
FROM Customers
3
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
4
ORDER BY Customers.CustomerName;

A selection from the result set may look like this:

Note: The FULL OUTER JOIN keyword returns all matching records from both tables whether the other table matches or not. So, if there are rows in "Customers" that do not have matches in "Orders", or if there are rows in "Orders" that do not have matches in "Customers", those rows will be listed as well.

12. Union (All) for Two Tables

UNION Operator:

The UNION operator is used to combine the result-set of two or more SELECT statements.

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

UNION Syntax

3
1
SELECT column_name(s) FROM table1
2
UNION
3
SELECT column_name(s) FROM table2;

UNION ALL Syntax

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:

3
1
SELECT column_name(s) FROM table1
2
UNION ALL
3
SELECT column_name(s) FROM table2;

Note: The column names in the result-set are usually equal to the column names in the first SELECT statement.

Example

• The following SQL statement returns the cities (only distinct values) from both the "Customers" and the "Suppliers" table:

4
1
SELECT City FROM Customers
2
UNION
3
SELECT City FROM Suppliers
4
ORDER BY City;

Note: If some customers or suppliers have the same city, each city will only be listed once, because UNION selects only distinct values. Use UNION ALL to also select duplicate values!

• The following SQL statement returns the cities (duplicate values also) from both the "Customers" and the "Suppliers" table:

4
1
SELECT City FROM Customers
2
UNION ALL
3
SELECT City FROM Suppliers
4
ORDER BY City;

• The following SQL statement returns the German cities (only distinct values) from both the "Customers" and the "Suppliers" table:

6
1
SELECT City, Country FROM Customers
2
WHERE Country='Germany'
3
UNION
4
SELECT City, Country FROM Suppliers
5
WHERE Country='Germany'
6
ORDER BY City;

• The following SQL statement returns the German cities (duplicate values also) from both the "Customers" and the "Suppliers" table:

6
1
SELECT City, Country FROM Customers
2
WHERE Country='Germany'
3
UNION ALL
4
SELECT City, Country FROM Suppliers
5
WHERE Country='Germany'
6
ORDER BY City;

• The following SQL statement lists all customers and suppliers:

5
1
SELECT 'Customer' AS Type, ContactName, City, Country
2
FROM Customers
3
UNION
4
SELECT 'Supplier', ContactName, City, Country
5
FROM Suppliers;

Notice the "AS Type" above - it is an alias. SQL Aliases are used to give a table or a column a temporary name. An alias only exists for the duration of the query. So, here we have created a temporary column named "Type", that list whether the contact person is a "Customer" or a "Supplier".

13. Group By in Table

GROUP BY Statement:

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

The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

GROUP BY Syntax

3
1
SELECT column_name(s)
2
FROM table_name
3
GROUP BY column_name(s)

Example

• The following SQL statement lists the number of customers in each country:

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

• The following SQL statement lists the number of customers in each country, sorted high to low:

4
1
SELECT COUNT(CustomerID), Country
2
FROM Customers
3
GROUP BY Country
4
ORDER BY COUNT(CustomerID) DESC;

• The following SQL statement lists the number of orders sent by each shipper:

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

14. Any, All in Table

ANY and ALL Operators:

The ANY and ALL operators allow you to perform a comparison between a single column value and a range of other values.

14.1 Any

The ANY operator:

• returns a boolean value as a result
• returns TRUE if ANY of the subquery values meet the condition

ANY means that the condition will be true if the operation is true for any of the values in the range.

ANY Syntax

xxxxxxxxxx
1
SELECT column_name(s)
2
FROM table_name1
3
WHERE column_name operator ANY
4
  (SELECT column_name
5
  FROM table_name1
6
  WHERE condition1);

Note: The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).

14.2 All

The ALL operator:

• returns a boolean value as a result
• returns TRUE if ALL of the subquery values meet the condition
• is used with SELECT, WHERE and HAVING statements

ALL means that the condition will be true only if the operation is true for all values in the range.

ALL Syntax With SELECT

xxxxxxxxxx
1
SELECT ALL column_name(s)
2
FROM table_name1
3
WHERE condition1;

ALL Syntax With WHERE or HAVING

xxxxxxxxxx
1
SELECT column_name(s)
2
FROM table_name1
3
WHERE column_name operator ALL
4
  (SELECT column_name
5
  FROM table_name1
6
  WHERE condition1);

Note: The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).

Example

• The following SQL statement lists the ProductName if it finds ANY records in the OrderDetails table has Quantity equal to 10 (this will return TRUE because the Quantity column has some values of 10):

6
1
SELECT ProductName
2
FROM Products
3
WHERE ProductID = ANY
4
  (SELECT ProductID
5
  FROM OrderDetails
6
  WHERE Quantity = 10);

• The following SQL statement lists ALL the product names:

3
1
SELECT ALL ProductName
2
FROM Products
3
WHERE TRUE;

• The following SQL statement lists the ProductName if ALL the records in the OrderDetails table has Quantity equal to 10. This will of course return FALSE because the Quantity column has many different values (not only the value of 10):

6
1
SELECT ProductName
2
FROM Products
3
WHERE ProductID = ALL
4
  (SELECT ProductID
5
  FROM OrderDetails
6
  WHERE Quantity = 10);

15. Select Into New Table or New Database

SELECT INTO Statement:

The SELECT INTO statement copies data from one table into a new table.

SELECT INTO Syntax

Copy all columns into a new table:

4
1
SELECT *
2
INTO newtable [IN externaldb]
3
FROM oldtable
4
WHERE condition;

Copy only some columns into a new table:

4
1
SELECT column1, column2, column3, ...
2
INTO newtable [IN externaldb]
3
FROM oldtable
4
WHERE condition;

Example

• The following SQL statement creates a backup copy of Customers:

2
1
SELECT * INTO CustomersBackup2017
2
FROM Customers;

• The following SQL statement uses the IN clause to copy the table into a new table in another database:

2
1
SELECT * INTO CustomersBackup2017 IN 'Backup.mdb'
2
FROM Customers;

• The following SQL statement copies only a few columns into a new table:

2
1
SELECT CustomerName, ContactName INTO CustomersBackup2017
2
FROM Customers;

• The following SQL statement copies only the German customers into a new table:

3
1
SELECT * INTO CustomersGermany
2
FROM Customers
3
WHERE Country = 'Germany';

• The following SQL statement copies data from more than one table into a new table:

4
1
SELECT Customers.CustomerName, Orders.OrderID
2
INTO CustomersOrderBackup2017
3
FROM Customers
4
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Tip: SELECT INTO can also be used to create a new, empty table using the schema of another. Just add a WHERE clause that causes the query to return no data:

3
1
SELECT * INTO newtable
2
FROM oldtable
3
WHERE 1 = 0;

16. Insert Into Select for Another Table

INSERT INTO SELECT Statement:

The INSERT INTO SELECT statement copies data from one table and inserts it into another table.

The INSERT INTO SELECT statement requires that the data types in source and target tables match.

Note: The existing records in the target table are unaffected.

INSERT INTO SELECT Syntax

Copy all columns from one table to another table:

xxxxxxxxxx
1
INSERT INTO table2
2
SELECT * FROM table1
3
WHERE condition1;

Copy only some columns from one table into another table:

xxxxxxxxxx
1
INSERT INTO table2 (column1, column2, column3, ...)
2
SELECT column1, column2, column3, ...
3
FROM table1
4
WHERE condition1;

Examples

• The following SQL statement copies "Suppliers" into "Customers" (the columns that are not filled with data, will contain NULL):

2
1
INSERT INTO Customers (CustomerName, City, Country)
2
SELECT SupplierName, City, Country FROM Suppliers;

• The following SQL statement copies "Suppliers" into "Customers" (fill all columns):

2
1
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
2
SELECT SupplierName, ContactName, Address, City, PostalCode, Country FROM Suppliers;

• The following SQL statement copies only the German suppliers into "Customers":

3
1
INSERT INTO Customers (CustomerName, City, Country)
2
SELECT SupplierName, City, Country FROM Suppliers
3
WHERE Country='Germany';

17. Case

CASE Expression:

The CASE expression goes through conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.

If there is no ELSE part and no conditions are true, it returns NULL.

CASE Syntax

6
1
CASE
2
    WHEN condition1 THEN result1
3
    WHEN condition2 THEN result2
4
    WHEN conditionN THEN resultN
5
    ELSE result
6
END;

Example

• The following SQL goes through conditions and returns a value when the first condition is met:

7
1
SELECT OrderID, Quantity,
2
CASE
3
    WHEN Quantity > 30 THEN 'The quantity is greater than 30'
4
    WHEN Quantity = 30 THEN 'The quantity is 30'
5
    ELSE 'The quantity is under 30'
6
END AS QuantityText
7
FROM OrderDetails;

• The following SQL will order the customers by City. However, if City is NULL, then order by Country:

7
1
SELECT CustomerName, City, Country
2
FROM Customers
3
ORDER BY
4
(CASE
5
    WHEN City IS NULL THEN Country
6
    ELSE City
7
END);

18. Null Functions

IFNULL(), ISNULL(), COALESCE(), and NVL() Functions:

Look at the following "Products" table:

Suppose that the "UnitsOnOrder" column is optional, and may contain NULL values.

Look at the following SELECT statement:

2
1
SELECT ProductName, UnitPrice * (UnitsInStock + UnitsOnOrder)
2
FROM Products;

In the example above, if any of the "UnitsOnOrder" values are NULL, the result will be NULL.

Solutions

MySQL

The MySQL IFNULL() function lets you return an alternative value if an expression is NULL:

2
1
SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0))
2
FROM Products;

or we can use the COALESCE() function, like this:

2
1
SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0))
2
FROM Products;