Tuesday, October 20, 2015
Monday, October 19, 2015
The SQL UNION
The SQL UNION Operator
The UNION operator is used to combine the result-set of two or more SELECT statements.Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.
SQL UNION Syntax
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
Note: The UNION operator selects only distinct values by default. To
allow duplicate values, use the ALL keyword with UNION.SQL UNION ALL Syntax
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
PS: The column names in the result-set of a UNION 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 |
| SupplierID | SupplierName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 | Exotic Liquid | Charlotte Cooper | 49 Gilbert St. | Londona | 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 the "Customers" and the "Suppliers" tables:Example
SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER
BY City;
City
Aachen
Albuquerque
Anchorage
Ann Arbor
Annecy
Århus
Barcelona
SQL FULL OUTER JOIN
SQL FULL OUTER JOIN Keyword
The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2).The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.
SQL 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 |
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 |
| 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 |
SQL RIGHT JOIN
SQL RIGHT JOIN Keyword
The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.SQL RIGHT JOIN Syntax
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;
or:
SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;
PS! 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 | Notes |
|---|---|---|---|---|---|
| 1 | Davolio | Nancy | 12/8/1968 | EmpID1.pic | Education includes a BA in psychology..... |
| 2 | Fuller | Andrew | 2/19/1952 | EmpID2.pic | Andrew received his BTS commercial and.... |
| 3 | Leverling | Janet | 8/30/1963 | EmpID3.pic | Janet has a BS degree in chemistry.... |
SQL RIGHT JOIN Example
The following SQL statement will return all employees, and any orders they have placed:Example
SELECT Orders.OrderID, Employees.FirstName
FROM Orders
RIGHT JOIN
Employees
ON Orders.EmployeeID=Employees.EmployeeID
ORDER BY
Orders.OrderID;
OrderID FirstName
Adam
10248
Steven
10249
Michael
10250
Margaret
10251
Janet
10252
Margaret
10253
Janet
10254
Steven
10255
Anne
10256
Janet
10257
Margaret
10258
Nancy
SQL LEFT JOIN
The LEFT JOIN keyword returns all rows from the left table (table1), with the
matching rows in the right table (table2). The result is NULL in the right side
when there is no match.
Below is a selection from the "Customers" table:
And a selection from the "Orders" table:
SQL LEFT JOIN Syntax
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
or:
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;
PS! 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 | 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 "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 |
SQL LEFT JOIN Example
The following SQL statement will return all customers, and any orders they might have:Example
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
The LEFT JOIN keyword returns all rows from the left table (table1), with the
matching rows in the right table (table2). The result is NULL in the right side
when there is no match.
SQL LEFT JOIN Syntax
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
or:
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;
PS! 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
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 "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
SQL LEFT JOIN Example
The following SQL statement will return 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;
ORDER BY Customers.CustomerName;
CustomerName OrderID
Alfreds Futterkiste
Ana Trujillo Emparedados y helados
10308
Antonio Moreno Taquería
10365
Around the Horn
10383
Around the Horn
10355
Berglunds snabbköp
10278
Berglunds snabbköp
10280
Berglunds snabbköp
10384
Blauer See Delikatessen
Blondel père et fils
10360
Blondel père et fils
10297
Blondel père et fils
10436
Blondel père et fils
10265
Bólido Comidas preparadas
10326
Bon app'
10331
Bon app'
10340
Bon app'
10362
Bottom-Dollar Marketse
10411
Bottom-Dollar Marketse
10431
Bottom-Dollar Marketse
10389
Bottom-Dollar Marketse
10410
B's Beverages
10289
Cactus Comidas para llevar
Centro comercial Moctezuma
10259
SQL JOIN
An SQL JOIN clause is used to combine rows from two or more tables, based on
a common field between them.
The most common type of join is: SQL INNER JOIN (simple join). An SQL INNER JOIN return all rows from multiple tables where the join condition is met.
Let's look at a selection from the "Orders" table:
Then, have a look at a selection from the "Customers" table:
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, if we run the following SQL statement (that contains an INNER JOIN):
The most common type of join is: SQL INNER JOIN (simple join). An SQL INNER JOIN return all rows from multiple tables where the join condition is met.
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, have a 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, if we run the following SQL statement (that contains an INNER JOIN):
Example
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM
Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;
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
SQL INNER JOIN Keyword
The INNER JOIN keyword selects all rows from both tables as long as there is
a match between the columns in both tables.
SQL INNER JOIN Syntax
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
or:
SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;
PS! INNER JOIN is the same as JOIN.
SQL INNER JOIN Example
The following SQL statement will return all customers with orders:
Example
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER
JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY
Customers.CustomerName;
CustomerName OrderID
Ana Trujillo Emparedados y helados
10308
Antonio Moreno Taquería
10365
Around the Horn
10383
Around the Horn
10355
Berglunds snabbköp
10278
Berglunds snabbköp
10280
Berglunds snabbköp
10384
Blondel père et fils
10265
Blondel père et fils
10436
Blondel père et fils
10297
Blondel père et fils
10360
Bólido Comidas preparadas
10326
Bon app'
10340
Bon app'
10362
Bon app'
10331
Bottom-Dollar Marketse
10411
Bottom-Dollar Marketse
10431
Bottom-Dollar Marketse
10389
Bottom-Dollar Marketse
10410
B's Beverages
10289
SQL Aliases
SQL Aliases
SQL aliases are used to give a database table, or a column in a table, a temporary name.Basically aliases are created to make column names more readable.
SQL Alias Syntax for Columns
SELECT column_name AS alias_name
FROM table_name;
SQL Alias Syntax for Tables
SELECT column_name(s)
FROM table_name AS alias_name;
Example
SELECT CustomerName AS Customer, ContactName AS [Contact Person]
FROM Customers;
Subscribe to:
Posts (Atom)