SQL: subqueries

Tweet

In this short post, I would like to introduce the concept of subqueries in SQL.

A subquery is a query statement inside another query statement. As you already know the order in which a statement is processed is quite important. That is a basic rule like in maths: writing something like 2+2*5 is different than writing (2+2)*5. The first operation gives 12, the second 20.
In SQL the principle is identical.

In order to explain what I'm saying, let's try an example.
Consider we have a table (rankingTable) with a column (rank) with numbers that indicates a ranking. The higher the number, the higher the rank.
We can get the highest rank with a query like:

SELECT MAX(rank) as highestRank
FROM rankingTable

Now we can use the above query and use it in a WHERE clause:

SELECT *
FROM rankingTable
WHERE rank = (SELECT MAX(rank) FROM rankingTable)

First, the subquery is executed, then the main query is executed considering the results of the subquery.

In general a subquery syntax can be explained like:

SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
      (SELECT column_name [, column_name ]
      FROM table1 [, table2 ]
      [WHERE])

The above is a SELECT statement, but subqueries can be used with INSERT, UPDATE and DELETE statements.


Subqueries are really powerful and the above examples are just a way of using them. In fact the subquery is in the WHERE clause. However they can be used directly in the SELECT statement or in the FROM clause as well.
As an example for the SELECT statement:

SELECT (number/(SELECT SUM(number) FROM table)) as myNumber
FROM table

The result of the above is myNumber which represents each number divided by the sum of all numbers.

That's all for today. I hope you find the above info interesting enough.