Advanced SQL programming

UNION 

The purpose of the SQL UNION query is to combine the results of two queries together. In this respect, UNION is somewhat similar to JOIN in that they are both used to related information from multiple tables. One restriction of UNION is that all corresponding columns need to be of the same data type. Also, when using UNION, only distinct values are selected (similar to SELECT DISTINCT).

The syntax is as follows:

[SQL Statement 1]
UNION
[SQL Statement 2]

Say we have the following two tables,
Table Store_Information

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
Los Angeles	$300	Jan-08-1999
Boston	$700	Jan-08-1999

Table Internet_Sales

Date	Sales
Jan-07-1999	$250
Jan-10-1999	$535
Jan-11-1999	$320
Jan-12-1999	$750

and we want to find out all the dates where there is a sales transaction. To do so, we use the following SQL statement:
SELECT Date FROM Store_Information
UNION
SELECT Date FROM Internet_Sales
Result:

Date

Jan-05-1999

Jan-07-1999

Jan-08-1999

Jan-10-1999

Jan-11-1999

Jan-12-1999

Please note that if we type "SELECT DISTINCT Date" for either or both of the SQL statement, we will get the same result set.

Union All

The purpose of the SQL UNION ALL command is also to combine the results of two queries together. The difference between UNION ALL and UNION is that, while UNION only selects distinct values, UNION ALL selects all values.

The syntax for UNION ALL is as follows:

[SQL Statement 1]
UNION ALL
[SQL Statement 2]

Let's use the same example as the previous section to illustrate the difference. Assume that we have the following two tables,
Table Store_Information

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
Los Angeles	$300	Jan-08-1999
Boston	$700	Jan-08-1999

Table Internet_Sales

Date	Sales
Jan-07-1999	$250
Jan-10-1999	$535
Jan-11-1999	$320
Jan-12-1999	$750

and we want to find out all the dates where there is a sales transaction at a store as well as all the dates where there is a sale over the internet. To do so, we use the following SQL statement:
SELECT Date FROM Store_Information
UNION ALL
SELECT Date FROM Internet_Sales
Result:

Date

Jan-05-1999

Jan-07-1999

Jan-08-1999

Jan-08-1999

Jan-07-1999

Jan-10-1999

Jan-11-1999

Jan-12-1999

Intersect

Similar to the UNION command, INTERSECT also operates on two SQL statements. The difference is that, while UNION essentially acts as an OR operator (value is selected if it appears in either the first or the second statement), the INTERSECT command acts as an AND operator (value is selected only if it appears in both statements).

The syntax is as follows:

[SQL Statement 1]
INTERSECT
[SQL Statement 2]

Let's assume that we have the following two tables,

Table Store_Information

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
Los Angeles	$300	Jan-08-1999
Boston	$700	Jan-08-1999

Table Internet_Sales

Date	Sales
Jan-07-1999	$250
Jan-10-1999	$535
Jan-11-1999	$320
Jan-12-1999	$750

and we want to find out all the dates where there are both store sales and internet sales. To do so, we use the following SQL statement:
SELECT Date FROM Store_Information
INTERSECT
SELECT Date FROM Internet_Sales
Result:

Date

Jan-07-1999

Please note that the INTERSECT command will only return distinct values.

Minus

The MINUS operates on two SQL statements. It takes all the results from the first SQL statement, and then subtract out the ones that are present in the second SQL statement to get the final answer. If the second SQL statement includes results not present in the first SQL statement, such results are ignored.

The syntax is as follows:

[SQL Statement 1]
MINUS
[SQL Statement 2]

Let's continue with the same example:

Table Store_Information

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
Los Angeles	$300	Jan-08-1999
Boston	$700	Jan-08-1999

Table Internet_Sales

Date	Sales
Jan-07-1999	$250
Jan-10-1999	$535
Jan-11-1999	$320
Jan-12-1999	$750

and we want to find out all the dates where there are store sales, but no internet sales. To do so, we use the following SQL statement:
SELECT Date FROM Store_Information
MINUS
SELECT Date FROM Internet_Sales
Result:

Date

Jan-05-1999

Jan-08-1999

"Jan-05-1999", "Jan-07-1999", and "Jan-08-1999" are the distinct values returned from "SELECT Date FROM Store_Information." "Jan-07-1999" is also returned from the second SQL statement, "SELECT Date FROM Internet_Sales," so it is excluded from the final result set.
Please note that the MINUS command will only return distinct values.
Some databases may use EXCEPT instead of MINUS. Please check the documentation for your specific database for the correct usage.

 Limit

Sometimes we may not want to retrieve all the records that satsify the critera specified in WHERE or HAVING clauses.

In MySQL, this is accomplished using the LIMIT keyword. The syntax for LIMIT is as follows:

[SQL Statement 1]
LIMIT [N]

where [N] is the number of records to be returned. Please note that the ORDER BY clause is usually included in the SQL statement. Without the ORDER BY clause, the results we get would be dependent on what the database default is

For example, we may wish to show the two highest sales amounts in Table Store_Information
Table Store_Information

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
San Francisco	$300	Jan-08-1999
Boston	$700	Jan-08-1999

we key in,
SELECT store_name, Sales, Date
FROM Store_Information
ORDER BY Sales DESC
LIMIT 2;

Result:

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
Boston	$700	Jan-08-1999

Top

In the previous section, we saw how LIMIT can be used to retrieve a subset of records in MySQL. In Microsoft SQL Server, this is accomplished using the TOP keyword.

The syntax for TOP is as follows:

SELECT TOP [TOP argument] "column_name"
FROM "table_name"

where [TOP argument] can be one of two possible types:

1. [N]: The first N records are returned.

2. [N'] PERCENT: The number of records corresponding to N'% of all qualifying records are returned.

For example, we may wish to show the two highest sales amounts in Table Store_Information
,

Table Store_Information

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
San Francisco	$300	Jan-08-1999
Boston	$700	Jan-08-1999

we key in,
SELECT TOP 2 store_name, Sales, Date
FROM Store_Information
ORDER BY Sales DESC;

Result:

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
Boston	$700	Jan-08-1999

Alternatively, if we want to show the top 25% of sales amounts from Table Store_Information, we key in,
SELECT TOP 25 PERCENT store_name, Sales, Date
FROM Store_Information
ORDER BY Sales DESC;

Result:

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999

Subquery

It is possible to embed a SQL statement within another. When this is done on the WHERE or the HAVING statements, we have a subquery construct.

The syntax is as follows:

SELECT "column_name1"
FROM "table_name1"
WHERE "column_name2" [Comparison Operator]
(SELECT "column_name3"
FROM "table_name2"
WHERE [Condition])

[Comparison Operator] could be equality operators such as =, >, <, >=, <=. It can also be a text operator such as "LIKE". The portion in red is considered as the "inner query", while the portion in green is considered as the "outer query".



Let's use the same example as we did to illustrate SQL joins:
Table Store_Information

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
Los Angeles	$300	Jan-08-1999
Boston	$700	Jan-08-1999

Table Geography

region_name	store_name
East	Boston
East	New York
West	Los Angeles
West	San Diego

and we want to use a subquery to find the sales of all stores in the West region. To do so, we use the following SQL statement:
SELECT SUM(Sales) FROM Store_Information
WHERE Store_name IN
(SELECT store_name FROM Geography
WHERE region_name = 'West')
Result:

SUM(Sales)

2050

In this example, instead of joining the two tables directly and then adding up only the sales amount for stores in the West region, we first use the subquery to find out which stores are in the West region, and then we sum up the sales amount for these stores.
In the above example, the inner query is first executed, and the result is then fed into the outer query. This type of subquery is called a simple subquery. If the inner query is dependent on the outer query, we will have a correlated subquery. An example of a correlated subquery is shown below:
SELECT SUM(a1.Sales) FROM Store_Information a1
WHERE a1.Store_name IN
(SELECT store_name FROM Geography a2
WHERE a2.store_name = a1.store_name)
Notice the WHERE clause in the inner query, where the condition involves a table from the outer query.

Exists

In the previous section, we used IN to link the inner query and the outer query in a subquery statement. IN is not the only way to do so -- one can use many operators such as >, <, or =. EXISTS is a special operator that we will discuss in this section.

EXISTS simply tests whether the inner query returns any row. If it does, then the outer query proceeds. If not, the outer query does not execute, and the entire SQL statement returns nothing.

The syntax for EXISTS is:

SELECT "column_name1"
FROM "table_name1"
WHERE EXISTS
(SELECT *
FROM "table_name2"
WHERE [Condition])

Please note that instead of *, you can select one or more columns in the inner query. The effect will be identical.
Let's use the same example tables:
Table Store_Information

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
Los Angeles	$300	Jan-08-1999
Boston	$700	Jan-08-1999

Table Geography

region_name	store_name
East	Boston
East	New York
West	Los Angeles
West	San Diego

and we issue the following SQL query:
SELECT SUM(Sales) FROM Store_Information
WHERE EXISTS
(SELECT * FROM Geography
WHERE region_name = 'West')
We'll get the following result:

SUM(Sales)

2750

At first, this may appear confusing, because the subquery includes the [region_name = 'West'] condition, yet the query summed up stores for all regions. Upon closer inspection, we find that since the subquery returns more than 0 row, the EXISTS condition is true, and the condition placed inside the inner query does not influence how the outer query is run.

Case

CASE is used to provide if-then-else type of logic to SQL. Its syntax is:

SELECT CASE ("column_name")
  WHEN "condition1" THEN "result1"
  WHEN "condition2" THEN "result2"
  ...
  [ELSE "resultN"]
  END
FROM "table_name"

"condition" can be a static value or an expression. The ELSE clause is optional.

In our Table Store_Information example,





Table Store_Information

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
San Francisco	$300	Jan-08-1999
Boston	$700	Jan-08-1999

if we want to multiply the sales amount from 'Los Angeles' by 2 and the sales amount from 'San Diego' by 1.5, we key in,
SELECT store_name, CASE store_name
  WHEN 'Los Angeles' THEN Sales * 2
  WHEN 'San Diego' THEN Sales * 1.5
  ELSE Sales
  END
"New Sales",
Date
FROM Store_Information

"New Sales" is the name given to the column with the CASE statement.
Result:

store_name	New Sales	Date
Los Angeles	$3000	Jan-05-1999
San Diego	$375	Jan-07-1999
San Francisco	$300	Jan-08-1999
Boston	$700	Jan-08-1999

NULL

In SQL, NULL means that data does not exist. NULL does not equal to 0 or an empty string. Both 0 and empty string represent a value, while NULL has no value.

Any mathematical operations performed on NULL will result in NULL. For example,

10 + NULL = NULL

Aggregate functions such as SUM, COUNT, AVG, MAX, and MIN exclude NULL values. This is not likely to cause any issues for SUM, MAX, and MIN. However, this can lead to confusion with AVG and COUNT. 



Let's take a look at the following example:
Table Sales_Data

store_name	Sales
Store A	300
Store B	200
Store C	100
Store D	NULL

Below are the results for each aggregate function:
SUM (Sales) = 600
AVG (Sales) = 200
MAX (Sales) = 300
MIN (Sales) = 100
COUNT (Sales) = 3
Note that the AVG function counts only 3 rows (the NULL row is excluded), so the average is 600 / 3 = 200, not 600 / 4 = 150. The COUNT function also ignores the NULL rolw, which is why COUNT (Sales) = 3.

ISNULL Function

The ISNULL function is available in both SQL Server and MySQL. However, their uses are different:

SQL Server

In SQL Server, the ISNULL() function is used to replace NULL value with another value.

For example, if we have the following table,

Table Sales_Data
store_name     Sales
Store A     300
Store B     NULL

The following SQL,

SELECT SUM(ISNULL(Sales,100)) FROM Sales_Data;
returns 400. This is because NULL has been replaced by 100 via the ISNULL function.
MySQL
In MySQL, the ISNULL() function is used to test whether an expression is NULL. If the expression is NULL, this function returns 1. Otherwise, this function returns 0.
For example,
ISNULL(3*3) returns 0
ISNULL(3/0) returns 1

IFNULL Function

The IFNULL() function is available in MySQL, and not in SQL Server or Oracle. This function takes two arguments. If the first argument is not NULL, the function returns the first argument. Otherwise, the second argument is returned. This function is commonly used to replace NULL value with another value. It is similar to the NVL function in Oracle and the ISNULL Function in SQL Server.

For example, if we have the following table,

Table Sales_Data
store_name     Sales
Store A     300
Store B     NULL

The following SQL,
SELECT SUM(IFNULL(Sales,100)) FROM Sales_Data;
returns 400. This is because NULL has been replaced by 100 via the ISNULL function.

NVL Function

The NVL() function is available in Oracle, and not in MySQL or SQL Server. This function is used to replace NULL value with another value. It is similar to the IFNULL Function in MySQL and the ISNULL Function in SQL Server.

For example, if we have the following table,

Table Sales_Data
store_name     Sales
Store A     300
Store B     NULL
Store C     150

The following SQL, 
SELECT SUM(NVL(Sales,100)) FROM Sales_Data;
returns 550. This is because NULL has been replaced by 100 via the ISNULL function, hence the sum of the 3 rows is 300 + 100 + 150 = 550.

Coalesce Function

The COALESCE function in SQL returns the first non-NULL expression among its arguments.

It is the same as the following CASE statement:

SELECT CASE ("column_name")
  WHEN "expression 1 is not NULL" THEN "expression 1"
  WHEN "expression 2 is not NULL" THEN "expression 2"
  ...
  [ELSE "NULL"]
  END
FROM "table_name"

For examples, say we have the following table, 
Table Contact_Info

Name	Business_Phone	Cell_Phone	Home_Phone
Jeff	531-2531	622-7813	565-9901
Laura	NULL	772-5588	312-4088
Peter	NULL	NULL	594-7477

and we want to find out the best way to contact each person according to the following rules:
1. If a person has a business phone, use the business phone number.
2. If a person does not have a business phone and has a cell phone, use the cell phone number.
3. If a person does not have a business phone, does not have a cell phone, and has a home phone, use the home phone number.
We can use the COALESCE function to achieve our goal:
SELECT Name, COALESCE(Business_Phone, Cell_Phone, Home_Phone) Contact_Phone
FROM Contact_Info;
Result:

Name	Contact_Phone
Jeff	531-2531
Laura	772-5588
Peter	594-7477

 NULLIF Function

The NULLIF function takes two arguments. If the two arguments are equal, then NULL is returned. Otherwise, the first argument is returned.

It is the same as the following CASE statement:

SELECT CASE ("column_name")
  WHEN "expression 1 = expression 2 " THEN "NULL"
  [ELSE "expression 1"]
  END
FROM "table_name"

For example, let's say we have a table that tracks actual sales and sales goal as below:
Table Sales_Data

Store_name	Actual	Goal
Store A	50	50
Store B	40	50
Store C	25	30

We want to show NULL if actual sales is equal to sales goal, and show actual sales if the two are different. To do this, we issue the following SQL statement:
SELECT Store_name, NULLIF(Actual,Goal) FROM Sales_Data;
The result is:

Store_name	NULLIF(Actual,Goal)
Store A	NULL
Store B	40
Store C	25

Rank

Displaying the rank associated with each row is a common request, and there is no straightforward way to do so in SQL. To display rank in SQL, the idea is to do a self-join, list out the results in order, and do a count on the number of records that's listed ahead of (and including) the record of interest. Let's use an example to illustrate. Say we have the following table,

Table Total_Sales
Name     Sales
John     10
Jennifer     15
Stella     20
Sophia     40
Greg     50
Jeff     20

we would type,

SELECT a1.Name, a1.Sales, COUNT(a2.sales) Sales_Rank
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.Sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;
Result:

Name	Sales	Sales_Rank
Greg	50	1
Sophia	40	2
Stella	20	3
Jeff	20	3
Jennifer	15	5
John	10	6

Let's focus on the WHERE clause. The first part of the clause, (a1.Sales <= a2.Sales), makes sure we are only counting the number of occurrences where the value in the Sales column is less than or equal to itself. If there are no duplicate values in the Sales column, this portion of the WHERE clause by itself would be sufficient to generate the correct ranking.
The second part of the clause, (a1.Sales=a2.Sales and a1.Name = a2.Name), ensures that when there are duplicate values in the Sales column, each one would get the correct rank.

Median

To get the median, we need to be able to accomplish the following:

    * Sort the rows in order and find the rank for each row.
    * Determine what is the "middle" rank. For example, if there are 9 rows, the middle rank would be 5.
    * Obtain the value for the middle-ranked row.

Let's use an example to illustrate. Say we have the following table,

Table Total_Sales
Name     Sales
John     10
Jennifer     15
Stella     20
Sophia     40
Greg     50
Jeff     20

we would type,
SELECT Sales Median FROM
(SELECT a1.Name, a1.Sales, COUNT(a1.Sales) Rank
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales < a2.Sales OR (a1.Sales=a2.Sales AND a1.Name <= a2.Name)
group by a1.Name, a1.Sales
order by a1.Sales desc) a3
WHERE Rank = (SELECT (COUNT(*)+1) DIV 2 FROM Total_Sales);
Result:

Median

20

You will find that Lines 2-6 are the same as how we find the rank of each row. Line 7 finds the "middle" rank. DIV is the way to find the quotient in MySQL, the exact way to obtain the quotient may be different with other databases. Finally, Line 1 obtains the value for the middle-ranked row.

Running Totals

Displaying running totals is a common request, and there is no straightforward way to do so in SQL. The idea for using SQL to display running totals similar to that for displaying rank: first do a self-join, then, list out the results in order. Where as finding the rank requires doing a count on the number of records that's listed ahead of (and including) the record of interest, finding the running total requires summing the values for the records that's listed ahead of (and including) the record of interest.

Let's use an example to illustrate. Say we have the following table,

Table Total_Sales

Name	Sales
John	10
Jennifer	15
Stella	20
Sophia	40
Greg	50
Jeff	20

we would type,
SELECT a1.Name, a1.Sales, SUM(a2.Sales) Running_Total
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;
Result:

Name	Sales	Running_Total
Greg	50	50
Sophia	40	90
Stella	20	110
Jeff	20	130
Jennifer	15	145
John	10	155

The combination of the WHERE clause and the ORDER BY clause ensure that the proper running totals are tabulated when there are duplicate values.

Percent To Total

To display percent to total in SQL, we want to leverage the ideas we used for rank/running total plus subquery. Different from what we saw in the SQL Subquery section, here we want to use the subquery as part of the SELECT. Let's use an example to illustrate. Say we have the following table,

Table Total_Sales
Name     Sales
John     10
Jennifer     15
Stella     20
Sophia     40
Greg     50
Jeff     20

we would type,
SELECT a1.Name, a1.Sales, a1.Sales/(SELECT SUM(Sales) FROM Total_Sales) Pct_To_Total
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;
Result:

Name	Sales	Pct_To_Total
Greg	50	0.3226
Sophia	40	0.2581
Stella	20	0.1290
Jeff	20	0.1290
Jennifer	15	0.0968
John	10	0.0645

The subquery "SELECT SUM(Sales) FROM Total_Sales" calculates the sum. We can then divide the individual values by this sum to obtain the percent to total for each row.

Cumulative Percent To Total

To display cumulative percent to total in SQL, we use the same idea as we saw in the Percent To Total section. The difference is that we want the cumulative percent to total, not the percentage contribution of each individual row. Let's use the following example to illuatrate:

Table Total_Sales
Name     Sales
John     10
Jennifer     15
Stella     20
Sophia     40
Greg     50
Jeff     20

we would type,
SELECT a1.Name, a1.Sales, SUM(a2.Sales)/(SELECT SUM(Sales) FROM Total_Sales) Pct_To_Total
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;
Result:

Name	Sales	Pct_To_Total
Greg	50	0.3226
Sophia	40	0.5806
Stella	20	0.7097
Jeff	20	0.8387
Jennifer	15	0.9355
John	10	1.0000

The subquery "SELECT SUM(Sales) FROM Total_Sales" calculates the sum. We can then divide the running total, "SUM(a2.Sales)", by this sum to obtain the cumulative percent to total for each row.