SQL SELECT INTO Statement

Toggle navigation
TUTORIAL HOME
SQL SELECT INTO Statement
❮ Previous Next ❯
The SQL 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:

SELECT *
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;
Copy only some columns into a new table:

SELECT column1, column2, column3, …
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;
The new table will be created with the column-names and types as defined in the old table. You can create new column names using the AS clause.

SQL SELECT INTO Examples
The following SQL statement creates a backup copy of Customers:

SELECT * INTO CustomersBackup2017
FROM Customers;
The following SQL statement uses the IN clause to copy the table into a new table in another database:

SELECT * INTO CustomersBackup2017 IN ‘Backup.mdb’
FROM Customers;
The following SQL statement copies only a few columns into a new table:

SELECT CustomerName, ContactName INTO CustomersBackup2017
FROM Customers;
The following SQL statement copies only the German customers into a new table:

SELECT * INTO CustomersGermany
FROM Customers
WHERE Country = ‘Germany’;
The following SQL statement copies data from more than one table into a new table:

SELECT Customers.CustomerName, Orders.OrderID
INTO CustomersOrderBackup2017
FROM Customers
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:

SELECT * INTO newtable
FROM oldtable
WHERE 1 = 0;

❮ Previous Next ❯

SQL UNION Operator

SQL UNION Operator
❮ Previous Next ❯
The SQL UNION Operator
The UNION operator is used to combine the result-set of two or more SELECT statements.

Each SELECT statement within UNION must have the same number of columns
The columns must also have similar data types
The columns in each SELECT statement must also be in the same order
UNION Syntax
SELECT column_name(s) FROM table1
UNION
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:

SELECT column_name(s) FROM table1
UNION ALL
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 in the UNION.

Demo Database
In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the “Customers” table:

CustomerID CustomerName ContactName Address City PostalCode Country
1

Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
And a selection from the “Suppliers” table:

SupplierID SupplierName ContactName Address City PostalCode Country
1 Exotic Liquid Charlotte Cooper 49 Gilbert St. London EC1 4SD UK
2 New Orleans Cajun Delights Shelley Burke P.O. Box 78934 New Orleans 70117 USA
3 Grandma Kelly’s Homestead Regina Murphy 707 Oxford Rd. Ann Arbor 48104 USA
SQL UNION Example
The following SQL statement selects all the different cities (only distinct values) from “Customers” and “Suppliers”:

Example
SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
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!

SQL UNION ALL Example
The following SQL statement selects all cities (duplicate values also) from “Customers” and “Suppliers”:

Example
SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;
»
SQL UNION With WHERE
The following SQL statement selects all the different German cities (only distinct values) from “Customers” and “Suppliers”:

Example
SELECT City, Country FROM Customers
WHERE Country=’Germany’
UNION
SELECT City, Country FROM Suppliers
WHERE Country=’Germany’
ORDER BY City;
»
SQL UNION ALL With WHERE
The following SQL statement selects all German cities (duplicate values also) from “Customers” and “Suppliers”:

Example
SELECT City, Country FROM Customers
WHERE Country=’Germany’
UNION ALL
SELECT City, Country FROM Suppliers
WHERE Country=’Germany’
ORDER BY City;
»
Another UNION Example
The following SQL statement lists all customers and suppliers:

Example
SELECT ‘Customer’ As Type, ContactName, City, Country
FROM Customers
UNION
SELECT ‘Supplier’, ContactName, City, Country
FROM Suppliers;
»

❮ Previous Next ❯

SQL ANY and ALL Operators

Toggle navigation
TUTORIAL HOME
SQL ANY and ALL Operators
❮ Previous Next ❯
The SQL ANY and ALL Operators
The ANY and ALL operators are used with a WHERE or HAVING clause.

The ANY operator returns true if any of the subquery values meet the condition.

The ALL operator returns true if all of the subquery values meet the condition.

ANY Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
(SELECT column_name FROM table_name WHERE condition);
ALL Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
(SELECT column_name FROM table_name WHERE condition);
Note: The operator must be a standard comparison operator (=, , !=, >, >=, <, or <=).

Demo Database
Below is a selection from the “Products” table in the Northwind sample database:

ProductID ProductName SupplierID CategoryID Unit Price
1 Chais 1 1 10 boxes x 20 bags 18
2 Chang 1 1 24 – 12 oz bottles 19
3 Aniseed Syrup 1 2 12 – 550 ml bottles 10
4 Chef Anton’s Cajun Seasoning 2 2 48 – 6 oz jars 21.35
5 Chef Anton’s Gumbo Mix 2 2 36 boxes 25
And a selection from the “OrderDetails” table:

OrderDetailID OrderID ProductID Quantity
1 10248 11 12
2 10248 42 10
3 10248 72 5
4 10249 14 9
5 10249 51 40
SQL ANY Examples
The ANY operator returns TRUE if any of the subquery values meet the condition.

The following SQL statement returns TRUE and lists the productnames if it finds ANY records in the OrderDetails table that quantity = 10:

Example
SELECT ProductName
FROM Products
WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);

»
The following SQL statement returns TRUE and lists the productnames if it finds ANY records in the OrderDetails table that quantity > 99:

Example
SELECT ProductName
FROM Products
WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity > 99);

»
SQL ALL Example
The ALL operator returns TRUE if all of the subquery values meet the condition.

The following SQL statement returns TRUE and lists the productnames if ALL the records in the OrderDetails table has quantity = 10:

Example
SELECT ProductName
FROM Products
WHERE ProductID = ALL (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);

❮ Previous Next ❯

SQL FULL OUTER JOIN Keyword

Toggle navigation
TUTORIAL HOME
SQL FULL OUTER JOIN Keyword
❮ Previous Next ❯
SQL FULL OUTER JOIN Keyword
The FULL OUTER JOIN keyword return all records when there is a match in either left (table1) or right (table2) table records.

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

FULL OUTER JOIN Syntax
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;
Demo Database
In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the “Customers” table:

CustomerID CustomerName ContactName Address City PostalCode Country
1

OrderID CustomerID EmployeeID OrderDate ShipperID
10308 2 7 1996-09-18 3
10309 37 3 1996-09-19 1
10310 77 8 1996-09-20 2
SQL FULL OUTER JOIN Example
The following SQL statement selects all customers, and all orders:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
A selection from the result set may look like this:

CustomerName OrderID
Alfreds Futterkiste
Ana Trujillo Emparedados y helados 10308
Antonio Moreno Taquería 10365
10382
10351
Note: The FULL OUTER JOIN keyword returns all the rows from the left table (Customers), and all the rows from the right table (Orders). 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.

❮ Previous Next ❯

SQL Self JOIN

Toggle navigation
TUTORIAL HOME
SQL Self JOIN
❮ Previous Next ❯
SQL Self JOIN
A self JOIN is a regular join, but the table is joined with itself.

Self JOIN Syntax
SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;
Demo Database
In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the “Customers” table:

CustomerID CustomerName ContactName Address City PostalCode Country
1

Example
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;
»

❮ Previous Next ❯

SQL RIGHT JOIN Keyword

Toggle navigation
TUTORIAL HOME
SQL RIGHT JOIN Keyword
❮ Previous Next ❯
SQL RIGHT JOIN Keyword
The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.

RIGHT JOIN Syntax
SELECT column_name(s)
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;
Note: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

Demo Database
In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the “Orders” table:

OrderID CustomerID EmployeeID OrderDate ShipperID
10308 2 7 1996-09-18 3
10309 37 3 1996-09-19 1
10310 77 8 1996-09-20 2
And a selection from the “Employees” table:

EmployeeID LastName FirstName BirthDate Photo
1 Davolio Nancy 12/8/1968 EmpID1.pic
2 Fuller Andrew 2/19/1952 EmpID2.pic
3 Leverling Janet 8/30/1963 EmpID3.pic
SQL RIGHT JOIN Example
The following SQL statement will return all employees, and any orders they might have have placed:

Example
SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;

»
Note: The RIGHT JOIN keyword returns all records from the right table (Employees), even if there are no matches in the left table (Orders).

❮ Previous Next ❯

TUTORIAL HOME SQL Joins

Toggle navigation
TUTORIAL HOME
SQL Joins
❮ Previous Next ❯
SQL JOIN
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Let’s look at a selection from the “Orders” table:

OrderID CustomerID OrderDate
10308 2 1996-09-18
10309 37 1996-09-19
10310 77 1996-09-20
Then, look at a selection from the “Customers” table:

CustomerID CustomerName ContactName Country
1 Alfreds Futterkiste Maria Anders Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Mexico
3 Antonio Moreno Taquería Antonio Moreno Mexico
Notice that the “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 the following SQL statement (that contains an INNER JOIN), that selects records that have matching values in both tables:

Example
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
»
and it will produce something like this:

OrderID CustomerName OrderDate
10308 Ana Trujillo Emparedados y helados 9/18/1996
10365 Antonio Moreno Taquería 11/27/1996
10383 Around the Horn 12/16/1996
10355 Around the Horn 11/15/1996
10278 Berglunds snabbköp 8/12/1996
Different Types of SQL JOINs
Here are the different types of the JOINs in SQL:

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

❮ Previous Next ❯

SQL LEFT JOIN Keyword

TUTORIAL HOME
SQL LEFT JOIN Keyword
❮ Previous Next ❯
SQL LEFT JOIN Keyword
The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.

LEFT JOIN Syntax
SELECT column_name(s)
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;
Note: In some databases LEFT JOIN is called LEFT OUTER JOIN.

Demo Database
In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the “Customers” table:

CustomerID CustomerName ContactName Address City PostalCode Country
1

OrderID CustomerID EmployeeID OrderDate ShipperID
10308 2 7 1996-09-18 3
10309 37 3 1996-09-19 1
10310 77 8 1996-09-20 2
SQL LEFT JOIN Example
The following SQL statement will select all customers, and any orders they might have:

Example
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;
»
Note: The LEFT JOIN keyword returns all records from the left table (Customers), even if there are no matches in the right table (Orders).

❮ Previous Next ❯

SQL Aliases

Toggle navigation
TUTORIAL HOME
SQL Aliases
❮ Previous Next ❯
SQL 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 the query.

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;
Demo Database
In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the “Customers” table:

CustomerID CustomerName ContactName Address City PostalCode Country
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
And a selection from the “Orders” table:

OrderID CustomerID EmployeeID OrderDate ShipperID
10354 58 8 1996-11-14 3
10355 4 6 1996-11-15 1
10356 86 6 1996-11-18 2
Alias for Columns Examples
The following SQL statement creates two aliases, one for the CustomerID column and one for the CustomerName column:

Example
SELECT CustomerID as ID, CustomerName AS Customer
FROM Customers;
»
The following SQL statement creates two aliases, one for the CustomerName column and one for the ContactName column. Note: It requires double quotation marks or square brackets if the alias name contains spaces:

Example
SELECT CustomerName AS Customer, ContactName AS [Contact Person]
FROM Customers;
»
The following SQL statement creates an alias named “Address” that combine four columns (Address, PostalCode, City and Country):

Example
SELECT CustomerName, Address + ‘, ‘ + PostalCode + ‘ ‘ + City + ‘, ‘ + Country AS Address
FROM Customers;
»
Note: To get the SQL statement above to work in MySQL use the following:

SELECT CustomerName, CONCAT(Address,’, ‘,PostalCode,’, ‘,City,’, ‘,Country) AS Address
FROM Customers;
Alias for 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):

Example
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;
»
The following SQL statement is the same as above, but without aliases:

Example
SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName
FROM Customers, Orders
WHERE Customers.CustomerName=”Around the Horn” AND Customers.CustomerID=Orders.CustomerID;
»
Aliases 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

❮ Previous Next ❯

SQL INNER JOIN Keyword

Toggle navigation
TUTORIAL HOME
SQL INNER JOIN Keyword
❮ Previous Next ❯
SQL INNER JOIN Keyword
The INNER JOIN keyword selects records that have matching values in both tables.

INNER JOIN Syntax
SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;
Demo Database
In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the “Orders” table:

OrderID CustomerID EmployeeID OrderDate ShipperID
10308 2 7 1996-09-18 3
10309 37 3 1996-09-19 1
10310 77 8 1996-09-20 2
And a selection from the “Customers” table:

CustomerID CustomerName ContactName Address City PostalCode Country
1

Example
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
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 show!

JOIN Three Tables
The following SQL statement selects all orders with customer and shipper information:

Example
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);
»

❮ Previous Next ❯