Advanced SQL: Explore Details of Your SQL Commands

SQL or Structured Query Language is a special purpose programming language used to manage a relational database system (RDBMS). A basic SQL statement includes operations such as deleting content or a record from a table, updating records in a table or inserting new data into a table. Advanced SQL focuses on the design of more complicated SQL strategies and statements. In addition, it also focuses  on joins, views and subqueries. Joins and subqueries are interchangeable. However, SQL uses  subqueries and joins views to hide a query’s complexity.

Learn SQL from scratch using a tutorial at Udemy.com

UNION and UNION ALL operator:

For combining a result set of two or more statements of SELECT command,  UNION and UNION ALL operators are used. The columns which would be combined should have the data types and columns in same order in each SELECT statement.

UNION:

By default, the UNION operator only selects distinct values .

General syntax:

SELECT column-name(s) FROM table1
UNION
SELECT column-name(s) FROM table2;

Learn more about SQL at Udemy.com

EXAMPLE:

Let us create two tables of name “Producers” and “Receivers” for our example.

Producers

Receivers

SELECT Country FROM Producers

UNION
SELECT Country FROM Receivers
ORDER BY Country;

This example will not include the duplicate values from column Country in the Producers table.

UNION ALL:

This operator will include the duplicate values too from column Country in table Producers and arrange them according to Country.

General syntax:

SELECT column-name(s) FROM table1
UNION
SELECT column-name(s) FROM table2;

Example:

SELECT Country FROM Producers
UNION ALL
SELECT Country FROM Receivers
ORDER BY Country;

This will select the duplicate values too from column country.

Inline View:

You can use a subquery in the “FROM” clause of your SQL statements. Inline views let you to treat the subquery as a predefined table or view. It exists only in the query under which it is created.

General syntax:

SELECT “column-name” FROM (inline view);

INTERSECT operator:

The INTERSECT operator is similar to the UNION command and operates on two SQL statements. The difference between INTERSECT and UNION is that UNION acts as an OR operator whereas INTERSECT operator acts as AND operator.

General syntax:

SQL statement1
INTERSECT
SQL statement2;

Example:

Let’s take our previous “Producers” and “Receivers” table.

SELECT Country FROM Producers
INTERSECT
SELECT Country FROM Receivers;

This example will select those countries that are present in both the Producers and Receivers tables.

Output:

Country

Italy

MINUS command:

The MINUS command also operates on two SQL statements. The MINUS command keeps result from the first statement and then subtracts the result obtained from the second statement from the first statement result to get the final result. If the second statement contains the results that are not present in first one, then that result will be ignored. It should be noted that the MINUS command only selects distinct values.

Learn more about SQL Database from scratch through a tutorial at Udemy.com

General syntax:

SQL statement1
MINUS
SQL statemnt2;

Example:

Let us take our “Producers” and “Receivers” table for this example.

SELECT Country FROM Producers
MINUS
SELECT Country FROM Receiver;

Output:

Country

Germany

Explanation:

The first statement will return a result that contains Country as Italy and Germany whereas the second statement will produce a result that contains Country as Italy and Mexico. The second statement contains a result that is not present in the first statement such as Mexico, which would be ignored and Italy would be subtracted from the first statement. The result would give the final result as “Germany”.

LIMIT command:

In SQL, all the results that satisfy the conditions are returned by default. But sometimes, you do not want to retrieve all your raw data. Therefore, the LIMIT command is used to retrieve a subset of all your records. Additionally, with your LIMIT command, the ORDER BY command is typically used to sort the list by a specific column. You can use multiple columns to sort your list.

General syntax:

SQL statement1
LIMIT [N];
[N] Is the number of values which we want to display.

Example:

Let’s create a table named “Producers” as an example.

SELECT ProID,ProName,ContactName,Country
FROM Producers
ORDER BY ProID DESC
LIMIT 2;

Now this example will only show two rows from table Producers in descending order.

Subquery:

A subquery gives you the flexibility to add one SQL statement into another SQL statement.

General syntax:

SELECT column-name1
FROM table-name1
WHERE column-name2 [comparison operator]
(SELECT column-name3 FROM table-name2 WHERE condition);

The statement “(SELECT column-name3 FROM table-name2 WHERE condition);” is considered an inner query whereas the statement“SELECT column-name1 FROM table-name1 WHERE column-name2 [comparison operator]” is considered an outer query.

Example:

SELECT ProName FROM Producers
WHERE Country IN
(SELECT Country  FROM Receivers WHERE ConatactName=’Angela’);

Output:

ProName

Nico

Exists:

In the above example, an IN statement is used for linking inner query with the outer query. We can also use other operators such as <,> or =. Similarly, EXISTS is a special kind of operator that checks whether the inner query returns a row or not. The outer query will proceed only if the  inner query returns any rows, otherwise it will not be executed.

General syntax:

SELECT column-name1
FROM table-name1
WHERE EXISTS
(SELECT * FROM table-name2 WHERE condition);

CASE:

CASE is used for providing an if-then-else conditional logic to your SQL statements . There are two types of CASE statements in SQL.

Simple CASE:

With simple CASE, you compare the expression with the static values.

Syntax:

SELECT CASE (column-name)
WHEN value1 THEN result1
WHEN value2 THEN result2
…….
ELSE result
END
FROM table-name;

The clause ELSE is optional.

Searched CASE:

In searched CASE, the expressions are compared with one or more logical conditions.

Syntax:

SELECT CASE
WHEN condition3 THEN result3
WHEN condition4 THEN result4
ELSE result
END
FROM table-name;

The ELSE clause is optional but “condition” can contain one or more than one logical statements.