Blog

SQL Subqueries Examples

In this article I want to help you and anyone who has doubts about how subqueries work with various simple and very graphic examples that clear up doubts.

Content

  • Example 1 Nested query
  • Example 2 SQL subquery
  • Complete example of nested queries
    • Nested query that returns a single result
    • Nested query that returns a column
  • Examples Conclusion

Example 1 Nested query

The example below is a typical example of a subquery returning a single result to the main query.

SELECT *
FROM `tabla`
WHERE `columna` = (
    SELECT `valor`
    FROM `tabla2`
    LIMIT 1
  )

This example shows an inner query (the one for “table2”) that returns a single value. The outer query, on the other hand, gets the results of “table” and filters the results from the value returned by the inner query, making the indicated “column” equal to the result of the subquery.

Note: You can use any equality operator such as =, >, <, >=, <=, or <>.

Example 2 SQL subquery

A nested query can also return an entire column, that is, all the values ​​of the different records in that column. Therefore, the outer query can use the IN command to filter out the rows that have one of the values ​​returned by the subquery. The example below highlights that case:

SELECT *
FROM tabla
WHERE `columna` IN (
    SELECT `columna`
    FROM  tabla2
    WHERE `clave_ajena` = 36
  )

Complete example of nested queries

The rest of the article presents concrete examples using subqueries.

Let’s imagine a website that allows you to ask and answer questions. Such a site has a database with a table for the questions and another for the answers.

Table “question” :

id date qualification content
1 2013-03-24 12:54:32 how to repair a computer Hello, my computer is broken, how can I proceed to repair it?
2 2013-03-26 19:27:41 How do I change a tire? What is the best way to change a tire easily?
3 2013-04-18 20:09:56 What to do if a device breaks? Is it better to repair electrical appliances or buy new ones?
4 2013-04-22 17:14:27 How do I clean a computer keyboard? Hello, there is a lot of dust under my computer keyboard, how do I clean it? Thank you. Thank you.

Table “response” :

id question_id date reply
1 1 2013-03-27 07:44:32 Good morning sir. Can you explain what is wrong with your computer? Thank you. Thank you. Thank you.
2 1 2013-03-28 19:27:11 Good evening, the simplest way is to call a professional to repair a computer. Sincerely,
3 2 2013-05-09 22:10:09 Tips on this topic can be found on the Internet.
4 3 2013-05-24 09:47:12 Good morning sir. It depends on you, your budget and your environmental preferences. Sincerely,

Nested query that returns a single result

With such an application, it can be useful to know the question related to the last answer added in the application. This can be done through the following SQL query:

SELECT *
FROM `pregunta`
WHERE pregunta.id= (
    SELECT id_pregunta
    FROM `respuesta`
    ORDER BY respuesta.fecha DESC
    LIMIT 1
  )

Such a request will return the following line:

id date qualification content
3 2013-04-18 20:09:56 What to do if a device breaks? Is it better to repair electrical appliances or buy new ones?

This result shows that the question related to the last answer in the forum is correct from this result.

Nested query that returns a column

Now let’s imagine we want to get the related questions with all the answers between 2 dates. These questions can be retrieved by the following SQL query:

SELECT *
FROM `regunta
WHERE pregunta.id IN (
    SELECT id_pregunta
    FROM respuesta
    WHERE fecha BETWEEN '2020-01-01' AND '2020-12-31'
  )

In our case, this query will return the following results:

id date qualification content
1 2013-03-24 12:54:32 how to repair a computer Hello, my computer is broken, how can I proceed to repair it?
2 2013-03-26 19:27:41 How do I change a tire? What is the best way to change a tire easily?
3 2013-04-18 20:09:56 What to do if a device breaks? Is it better to repair electrical appliances or buy new ones?

Such a query allows you to retrieve the questions that have been answered between 2 dates. This is handy in our case to avoid getting replies that haven’t had any replies or no new replies for a long time.

Examples Conclusion

I hope these simple examples have been useful to you, if you have found them useful, do not hesitate to leave a comment and share the content so that I can continue making articles like this.

Leave a Reply