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.
Recommended Articles
Top courses in SQL
SQL students also learn
Empower your team. Lead the industry.
Get a subscription to a library of online courses and digital learning tools for your organization with Udemy Business.