fastread homefastrread library fastread menu

MariaDB : Subqueries

Tutorial by:      Date: 2016-04-18 05:35:22

❰ Previous Next ❱

This MariaDB tutorial explains how to use subqueries in MariaDB with syntax and examples.

What is a subquery in MariaDB?

In MariaDB, a subquery is a query within a query. You can create subqueries within your SQL statements. These subqueries can reside in the WHERE clause, the FROM clause, or the SELECT clause.

Note

  • In MariaDB, a subquery is also called an INNER QUERY or INNER SELECT.
  • In MariaDB, the main query that contains the subquery is also called the OUTER QUERY or OUTER SELECT.

WHERE clause

Most often, the subquery will be found in the WHERE clause. These subqueries are also called nested subqueries.

For example:

SELECT s.site_id, s.site_name

FROM sites s

WHERE s.site_id IN

   (SELECT p.site_id

    FROM pages p

    WHERE p.file_size > 89);

The subquery portion of the SELECT statement above is:

(SELECT p.site_id

 FROM pages p

 WHERE p.file_size > 89);

This subquery allows you to find all site_id values from the pages table that have a file_size greater than 89. The subquery is then used to filter the results from the main query using the IN condition.

This subquery could have alternatively been written as an INNER join as follows:

SELECT s.site_id, s.site_name

FROM sites s

INNER JOIN pages p

ON s.site_id = p.site_id

WHERE p.file_size > 89;

This INNER JOIN would run more efficiently than the original subquery. It is important to note, though, that not all subqueries can be rewritten using joins.

FROM clause

A subquery can also be found in the FROM clause. These are called inline views.

For example:

SELECT sites.site_name, subquery1.total_size

FROM sites,

 (SELECT site_name, SUM(file_size) AS total_size

  FROM pages

  GROUP BY site_name) subquery1

WHERE subquery1.site_name = sites.site_name;

In this example, we've created a subquery in the FROM clause as follows:

(SELECT site_name, SUM(file_size) AS total_size

 FROM pages

 GROUP BY site_name) subquery1

This subquery has been aliased with the name subquery1. This will be the name used to reference this subquery or any of its fields.

SELECT clause

A subquery can also be found in the SELECT clause. These are generally used when you wish to retrieve a calculation using an aggregate function such as the SUM, COUNT, MIN, MAX , or AVG function, but you do not want the aggregate function to apply to the main query.

For example:

SELECT p1.site_name,

  (SELECT MAX(file_size)

   FROM pages p2

   WHERE p1.site_id = p2.site_id) subquery2

FROM pages p1;

In this example, we've created a subquery in the SELECT clause as follows:

(SELECT MAX(file_size)

 FROM pages p2

 WHERE p1.site_id = p2.site_id) subquery2

The subquery has been aliased with the name subquery2. This will be the name used to reference this subquery or any of its fields.

The trick to placing a subquery in the SELECT clause is that the subquery must return a single value. This is why an aggregate function such as the SUM, COUNT, MIN, MAX, or AVG function is commonly used in the subquery.

 

❰ Previous Next ❱


MariaDB

Submit Your Thought, Tutorial, Articls etc.

Submit Your Information India's Number one online promotion website