MYSQL-TOOLS

Comparison of graphical tools for MySQL

Home Advanced Queries

Advanced Queries

In this lesson of the MySQL tutorial, you will learn...
  1. How to use SELECT statements to retrieve calculated values.
  2. To write queries with subqueries.
  3. To select columns from multiple tables with joins.
  4. To select records from multiple tables with unions.
  5. How to work with aggregate functions and grouping.
  6. How to work with SQL's data manipulation functions.
  7. How to use full and outer joins in SELECT, UPDATE, and DELETE statements that link together two or more tables
  8. How to add subqueries to your SELECT, UPDATE, and DELETE statements that retrieve data that can be used by those statements
  9. How to create unions that join together two SELECT statements
  10. How to create and use Views



Advanced Queries

Print PDF
User Rating: / 4
PoorBest 

More on The SELECT Statement

The SELECT statement is one of the most powerful SQL statements in MySQL. It provides a great deal of flexibility and allows you to create queries that are as simple or as complex as you need to make them. The syntax for a SELECT statement is made up of a number of clauses and other elements, most of which are optional, that allow you to make your query more specific to the information being sought. The following syntax describes the elements that make up a SELECT statement:

<select statement>::=
SELECT
[{ALL | DISTINCT | DISTINCTROW}
| HIGH_PRIORITY
| {SQL_BIG_RESULT | SQL_SMALL_RESULT}
| SQL_BUFFER_RESULT
| {SQL_CACHE | SQL_NO_CACHE}
| STRAIGHT_JOIN]
{* | {<column name> | <expression>} [[AS] <alias>]
[{, {<column name> | <expression>} [[AS] <alias>]}...]
}
[
FROM <table name> [{, <table name>}...]
[WHERE <expression> [{<operator> <expression>}...]]
[ORDER BY <column name> [ASC | DESC][{, <column name> [ASC | DESC]}...]]
[LIMIT [<offset>,] <row count>]
]

The SELECT statement syntax includes a number of optional clauses that help you define which rows your SELECT statement returns and how those rows display. Of particular importance to creating an effective SELECT statement are the WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT clauses. This lesson covers these clauses and their definition in various SELECT statements.

Using Variables in a SELECT Statement

One type of expression that you can include in your select list is one that allows you to define a variable. A variable is a type of placeholder that holds a value for the duration of a client session. This is useful if you want to reuse a value in later SELECT statements.

You define a variable by using the following structure:

Syntax
@<variable name>:={<column name> | <expression>} [[AS] <alias>]

The variable name is preceded by the at (@) symbol its value is specified by using the colon/equal sign (:=) symbols. In addition, a variable can be associated with only one value, so your SELECT statement should return only one value per variable. If your SELECT statement returns more than one value for a variable, the last value returned is used by the variable.

One can define more than one variable in a SELECT statement as a separate select list element.

For example, the following SELECT statement defines two variables:

There are three types of variables in MySQL:

  • Ordinary variables: Such variables are indicated by a prefixed @ sign. They lose their content at the close of the SQL connection.
  • System and server variables: Such variables contain states or attributes of the MySQL server. They are indicated by two prefixed @ signs (e.g., @@binlog_cache_size).

    Several system variables exist either specifically for the current connection (e.g., @@session.wait_timeout) or globally for the entire MySQL server (e.g., @@global.wait_timeout containing the default value for this variable).

  • Local variables and parameters within stored procedures: These variables are declared locally within an SP and are valid only within the stored procedure. They are not indicated by a special sign, but they must have names that are distinguishable from names of tables and columns.

Code Sample:

SET @varname = 3;
SELECT @varname := 3;
SELECT @language_id := language_id FROM language WHERE name = 'Italian';
SELECT COUNT(1) FROM film_detail WHERE language_id = @language_id INTO @filmcount;
SELECT @filmcount 'Italian Films';
Code Explanation

We store the language ID in a variable.

Then use that variable to count films into another variable.

We then list the film count variable with a title.

Note: In addition to a SELECT statement, you can use a SET statement to define a variable.

Creating Joins in Your SQL Statements

In a normalized database, groups of data are stored in individual tables, and relationships are established between those tables to link related data. As a result, often when creating SELECT, UPDATE, or DELETE statements, you want to be able to access data in different tables to carry out an operation affected by those relationships.

The real power of a relational database emerges from the ability to combine multiple entities in a single operation. For example, how can we find out

  • Which films are rented by which customers?
  • Which staff are associated with which store?

Such reports require data from multiple tables. Enter joins.

Syntax
<select statement>::=
SELECT ...
[FROM {<table reference> | <join definition>}
...]

<join definition>::=
{<table reference>, <table reference> [{, <table reference>}...]}
| {<table reference> [INNER | CROSS ] JOIN <table reference> [<join condition>]}
| {<table reference> STRAIGHT_JOIN <table reference>}
| {<table reference> LEFT [OUTER] JOIN <table reference> [<join condition>]}
| {<table reference> RIGHT [OUTER] JOIN <table reference> [<join condition>]}
| {<table reference> NATURAL [{LEFT | RIGHT} [OUTER]] JOIN <table reference>}

<join condition>::=
ON <expression> [{<operator> <expression>}...]
| USING (<column> [{, <column>}...])

To support the capability to access data in multiple tables, a join is a condition defined in a SELECT, UPDATE, or DELETE statement that links together two or more tables. Many elements of creating joins in a SELECT statement are the same elements used for UPDATE and DELETE joins.

The basic join is made up of only table references separated by commas. The other joins are the inner and cross joins, the straight join, the left join, the right join, and the natural join. Later in this section, you learn about each type of join.

Syntax
SELECT table1.column, table2.column
FROM table1 JOIN table2
ON (table1.column=table2.column)
WHERE conditions

Creating a report that returns the staff ID and inventory ID from the rental table is not difficult.

Code Sample:

SELECT staff_id, customer_id, inventory_id
FROM rental;

But this isn't very useful as we cannot tell the staff who booked this rental. The next sample shows how we can use a join to make the report more useful.

Code Sample:

SELECT s.staff_id, s.first_name, s.last_name, r.rental_id, r.rental_date
FROM staff s JOIN rental r ON
(s.staff_id = rental.staff_id)
ORDER BY r.rental_date;
Code Explanation

The rental table is joined with the staff table.

Using full table names as prefixes can make SQL queries unnecessarily wordy. Table aliases can make the code a little more concise. The example also illustrates the use of table aliases.

Inner Joins and Cross Joins

When it comes to joins, MySQL provides numerous ways to accomplish the same results. For example, you can create inner joins and cross joins that produce identical result sets as those generated by the basic joins.

By supporting different ways to achieve different results, MySQL makes your code can be more portable in an application to other RDBMS. For more complex joins, you might want to try different types of joins to determine whether one performs better than the other.

Now take a look at how an inner or cross join is created. The following syntax shows you how to add an inner or cross join to your SELECT statement:

Syntax
<table reference> [INNER | CROSS ] JOIN <table reference>
ON <expression> [{<operator> <expression>}...]
| USING (<column> [{, <column>}...])

We have a table reference, the optional INNER or CROSS keyword, the JOIN keyword, the second table reference, and an optional join condition. The join condition can consist of an ON clause or a USING clause. In the basic join, the match-up is defined in the WHERE clause, but in the inner and cross joins, this is accomplished through the join condition.

For all practical purposes, specifying the INNER or CROSS keywords produces the same results as the basic join. For example, the following statements produce the same results:

Code Sample:

SELECT l.name, f.title
FROM film_detail f
JOIN language l ON (f.language_id = l.language_id)
WHERE l.language_id <> 1
--AND (f.title LIKE 'W%' OR f.title IS NULL)
ORDER BY 1;

SELECT l.name, f.title
FROM film_detail f
INNER JOIN language l ON (f.language_id = l.language_id)
WHERE l.language_id <> 1
--AND (f.title LIKE 'W%' OR f.title IS NULL)
ORDER BY 1;

SELECT l.name, f.title
FROM film_detail f
CROSS JOIN language l USING (language_id)
WHERE l.language_id <> 1
--AND (f.title LIKE 'W%' OR f.title IS NULL)
ORDER BY 1;
Code Explanation

Different joins between film and language tables.

Each statement returns approximately 26 rows.

As you can see, the FROM clause defines the join. Rather than separating the joined tables with a comma, the statement uses the JOIN keyword.

In addition, the ON clause specifies that the language_id values in the film_detail and language tables must be equal in order for a row to be returned.

Multi-table Joins

Multi-table joins can get complex and may impose some performance penalties, but the syntax is relatively straightforward.

Syntax
SELECT table1.column, table2.column, table3.column
FROM table1
JOIN table2 ON (table1.column=table2.column)
JOIN table3 ON (table2.column=table3.column)
WHERE conditions

Note that, to join with a table, that table must be in the FROM clause or must already be joined with the table in the FROM clause. Consider the following.

SELECT table1.column, table2.column, table3.column
FROM table1
JOIN table3 ON (table2.column=table3.column)
JOIN table2 ON (table1.column=table2.column)
WHERE conditions

The above code would break because it attempts to join table3 with table2 before table2 has been joined with table1.

Code Sample:


SELECT s.staff_id, CONCAT_WS( ' ', s.first_name, s.last_name ) Staff,
c.customer_id, CONCAT_WS( ' ', c.first_name, c.last_name ) Customer,
r.rental_date,
r.rental_id
FROM
staff s JOIN rental r ON (s.staff_id = r.staff_id)
JOIN customer c ON (c.customer_id = r.customer_id)
WHERE r.rental_date > '20050815'
ORDER BY 3, 4;
Code Explanation

Join rental table with both staff and customer table with some criteria and formatting.

Exercise: Multi-table Joins

Duration: 10 to 20 minutes.

In this exercise, you will practice using joins.

  1. Add to the previous example, also show film title and rating, joining inventory and film tables to rental.
  2. Order by customer, film title and rental date.
  3. Restrict the list to customer 'PEGGY MYERS' and film 'MONSOON CAUSE'
  4. Format the rental and return dates to show year, month and day.

Outer Joins

So far, all the joins we have worked with are inner joins, meaning that rows are only returned that have matches in both tables. For example, when doing an inner join between the staff table and the rental table, only employees that have matching rental and rental that have matching employees will be returned.

As a point of comparison, let's first look at another inner join.

Code Sample:

SELECT cc.city, COUNT(DISTINCT s.staff_id) AS 'Staff Count'
FROM
city cc
JOIN address ca ON (ca.city_id = cc.city_id)
JOIN staff s ON (s.address_id = ca.address_id)
GROUP BY cc.city
ORDER BY 2 DESC;
Code Explanation

We list only those cities are listed which have staff living in them. There are only two staff, so the list is quite small.

Except for the joins that returned Cartesian products, the joins that you have seen so far have been those that returned only rows that contained matching values in the specified columns. Outer Joins allow us to include details that do not fit these conditions, while avoiding large result sets of unnecessary information.

You can create one of two types of outer joins: a left join or a right join. A left join pulls all values from the table on the left, and a right join pulls all values from the table listed on the right.

To help illustrate this point, next Figure shows a Venn diagram that represents the various outer join. The circle on the left represents the film table, and the circle on the right represents the customer table. The Venn diagram illustrates what will happen when these tables are joined together. In an Inner join, only matching join values are returned, that appear in the section where the two circles intersect. Outer Joins

If this were a right outer join, any customers that have not rented a film (not in join) are paired with NULL values, as is the case of the film for which no customers have rented.

Left Joins

A LEFT JOIN (also called a LEFT OUTER JOIN) returns all the records from the first table even if there are no matches in the second table.

The syntax for a left outer join is similar to an inner or cross join, except that you must include the LEFT keyword, as shown in the following syntax:

As you can see, you first specify a table reference, followed by the LEFT keyword, the optional OUTER keyword, and then the JOIN keyword. This is all followed by another table reference and an optional join condition, which can be an ON clause or a USING clause:

Syntax
<table reference> LEFT [OUTER] JOIN <table reference>
ON <expression> [{<operator> <expression>}...]
| USING (<column> [{, <column>}...])

All rows in table1 will be returned even if they do not have matches in table2.

Code Sample:

SELECT cc.city, COUNT(DISTINCT s.staff_id) AS 'Staff Count'
FROM
city cc
LEFT JOIN address ca ON (ca.city_id = cc.city_id)
LEFT JOIN staff s ON (s.address_id = ca.address_id)
WHERE cc.city > 'T'
GROUP BY cc.city
ORDER BY 2 DESC;
Code Explanation

We see that several cities are listed whether or not there are matching staff living in the cities. We filter the cities here to keep the result set small, the point is to show a LEFT JOIN.

Exercise: Left Outer Join

Duration: 10 to 20 minutes.

In this exercise, you will practice using left outer joins.

  1. List customer name from customer_detail and title, rating from rental_detail table, joining the two together.
  2. Restrict the rental list to films in Horror category.
  3. List customers even if they have not rented a film in that category.
  4. Limit the result via a filter on customer such as ID > 580.

Right Joins

A RIGHT OUTER JOIN returns all the records from the second table (on the right) even if there are no matches in the first table (on the left).

A right join is simply the counterpart to the left join and works the same as a left join, except that all values are retrieved from the table on the right. The following syntax shows how to define a right join in your FROM clause:

Syntax
<table reference> RIGHT [OUTER] JOIN <table reference>
ON <expression> [{<operator> <expression>}...]
| USING (<column> [{, <column>}...])
Syntax
SELECT table1.column, table2.column
FROM table1
RIGHT [OUTER] JOIN table2 ON (table1.column=table2.column)
WHERE conditions

All rows in table2 will be returned even if they do not have matches in table1.

The following SELECT statement shows a RIGHT JOIN:

Code Sample:

SELECT l.name, f.title
FROM film_detail f
RIGHT JOIN language l ON (f.language_id = l.language_id)
WHERE l.language_id <> 1
--AND (f.title LIKE 'W%' OR f.title IS NULL)
ORDER BY 1;
Code Explanation

All languages other than English are returned, whether a film was made in that language or not.

The title column contains a NULL value for some of the rows where no film was found in that language.

Full Outer Joins

A FULL OUTER JOIN returns all the records from each table even if there are no matches in the joined table.

Syntax
SELECT table1.column, table2.column
FROM table1
FULL [OUTER] JOIN table2 ON (table1.column=table2.column)
WHERE conditions

All rows in table1 and table2 will be returned.

Note: MySQL does not have support for FULL OUTER JOIN, but one can simulate the same using UNION between a LEFT and a RIGHT JOIN.

Code Sample:

SELECT l.name, f.title
FROM film_detail f
LEFT JOIN language l ON (f.language_id = l.language_id)
WHERE l.language_id <> 1 OR f.language_id IS NULL
UNION
SELECT l.name, f.title
FROM film_detail f
RIGHT JOIN language l ON (f.language_id = l.language_id)
WHERE l.language_id <> 1 OR f.title IS NULL
ORDER BY 1;
Code Explanation

All records in film and language tables will be counted, including null and missing entries.

Creating Full Joins

MySQL supports several types of full joins: the basic join, the inner and cross joins, and the straight join. Of these, the basic join is the one most commonly used, so that is where the discussion begins.

Creating Basic Joins

In some of the examples provided earlier in the book, you saw how to use basic joins. As you recall, you simply specified a FROM clause, along with multiple table names, separated by commas.

Code Sample:

SELECT l.name, f.title
FROM film_detail f, language l
WHERE (f.language_id = l.language_id)
AND l.language_id <> 1
AND (f.title LIKE 'W%' OR f.title IS NULL)
ORDER BY 1;
Code Explanation

An implicit join between languages and film using WHERE clause.

Creating Straight Joins

You would use a straight join in cases where it appears that the Join Optimizer is not processing the SELECT statement most efficiently. A straight join tells the join optimizer read the table on the left before the table on the right.

Tip: The join optimizer is a component of the MySQL server that tries to determine the best way to process a join. In most cases, though, you should rely on the optimizer.

To create a straight join, you can use the following syntax in your FROM clause:

Syntax
<table reference> STRAIGHT_JOIN <table reference>

Code Sample:

SELECT f.title, f.rating, f.category_name, 
CONCAT_WS(' ', c.first_name, c.last_name) AS Customer,
r.rental_date
FROM film_detail AS f
STRAIGHT_JOIN rental AS r
STRAIGHT_JOIN inventory AS i
STRAIGHT_JOIN customer AS c
WHERE
c.customer_id = r.customer_id
AND r.inventory_id = i.inventory_id
AND i.film_id = f.film_id
AND c.customer_id = 101
ORDER BY rental_date, title;
Code Explanation

A straight join between film, rental, inventory and customer table, the join is qualified in the WHERE clause.

Creating Natural Joins

Another type of join that you can specify in a SELECT statement is the natural join. This type of join can be specified as a full join, left join, or right join. The following syntax shows how to define a natural join in your FROM clause:

Syntax
<table reference> NATURAL [{LEFT | RIGHT} [OUTER]] JOIN <table reference>

Here, we do not define a join condition. A natural join automatically joins the columns in both tables that are common to each other. For example, if you were joining the rental table to the customer table, the natural join would automatically define that join on the customer_id column, which is common to both tables.

Code Sample:

DROP TEMPORARY TABLE join_table1;
DROP TEMPORARY TABLE join_table2;

CREATE TEMPORARY TABLE join_table1 (t1id INT, jcol INT);
CREATE TEMPORARY TABLE join_table2 (t2id INT, jcol INT);

INSERT INTO join_table1 VALUES(101,201);
INSERT INTO join_table1 VALUES(102,201);
INSERT INTO join_table1 VALUES(103,202);
INSERT INTO join_table1 VALUES(104,202);

INSERT INTO join_table2 VALUES(201,201);
INSERT INTO join_table2 VALUES(202,202);

SELECT j1.t1id, j2.t2id, j1.jcol FROM join_table1 j1 NATURAL JOIN join_table2 j2 WHERE j2.jcol = 201;
SELECT * FROM join_table1 j1 JOIN join_table2 j2 USING (jcol) WHERE j2.jcol = 202;
Code Explanation

Create and populate two temporary tables, with a common column name.

Natural joins using common columns between two temporary tables, no join condition is used.

Unions

Unions are used to retrieve a unified result set containing records from two distjunct statements, even to read multiple record sets from a single table. The result set appears to be coming from a single statement.

Code Sample:

SELECT CONCAT_WS( ' ', last_name, first_name ) 'People'
FROM staff
UNION
SELECT CONCAT_WS( ' ', last_name, first_name ) 'People'
FROM customer
WHERE last_name LIKE 'SU%'
;
Code Explanation

This query will return the names all employees and customers as a single resultset.

UNION ALL

By default, all duplicates are removed from the participating result sets in UNIONs. To include duplicates, use UNION ALL in place of UNION.

UNION Rules

  • Each query must return the same number of columns.
  • The columns must be in the same order.
  • Column datatypes must be compatible.

Unions generally make most sense when combining similar results from different sources, or when a complex or composite query reads better when broken into simpler sub-components.

Exercise: Using Unions

Duration: 10 to 20 minutes.

In this exercise, you will practice writing unions.

  1. Create a Unionized listing of the film title, rental_rate, rating and language name for films in languages other than 'English' and those with rating as 'PG'.
  2. Create a version that does not show the duplicates. Check if the row count is different between the two result sets.
  3. Order the list by film title.
  4. Note: Use film_detail table as almost all films in the original film table are in English.

Creating Views

Views are essentially saved SELECT queries that can themselves be queried. They are used to provide easier access to normalized data. For example, the rental table has information about an order. Although it references the employee and customer involved in each order, the rental doesn't itself contain any valuable information about the employee and customer. We have seen how to use joins to output valuable data from different tables. Creating a view is a way of saving these types of more complicated queries. Further, you can query views in the same way that you can query tables. You can even join them with other views or tables.

To create views, simply write your SELECT query and wrap it in a CREATE VIEW statement as shown below.

Syntax
CREATE VIEW view_name AS
SELECT statement goes here...

See film_list view in sakila, where the film table is read alongwith some joined data. The view would save the hassle of specifying joins everytime a similar query is needed.

Here is an example showing how to create a view from a complicated query we've seen earlier.

Code Sample:

CREATE VIEW film_rental AS
SELECT f.film_id, f.title, f.rating, f.category_name, c.customer_id,
r.rental_id,
CONCAT_WS(' ', c.first_name, c.last_name) AS Customer,
r.rental_date, r.return_date
FROM film_detail AS f
STRAIGHT_JOIN rental AS r
STRAIGHT_JOIN inventory AS i
STRAIGHT_JOIN customer AS c
WHERE
c.customer_id = r.customer_id
AND r.inventory_id = i.inventory_id
AND i.film_id = f.film_id
ORDER BY rental_date, title;

SELECT * FROM film_rental WHERE customer_id = 101 AND rental_date > '20050731';
Code Explanation

The SELECT on the view shows how easy it is to get information on specific rental for a given customer.

Note: Usually ORDER BY clause is not allowed in views, but MySQL allows it.

Dropping Views

Don't like your view? Dropping it is easy enough:

Syntax
DROP VIEW view_name

Code Sample:

DROP VIEW IF EXISTS film_rental;
Code Explanation

Simply drop the view (if it exists) that we just created.

Benefits of Views

Views have the following benefits:

  • Security - Views can be made accessible to users while the underlying tables are not directly accessible. This allows the DBA to give users only the data they need, while protecting other data in the same table.
  • Simplicity - Views can be used to hide and reuse complex queries.
  • Column Name Simplication or Clarification - Views can be used to provide aliases on column names to make them more memorable and/or meaningful.
  • Stepping Stone - Views can provide a tiered approach in a "multi-level" query systematically. For example, you could create a view of a query that counted the number of rental each staff had made. You could then query that view to group the sales people by the number of rental they had made.

Exercise: Creating a View

Duration: 15 to 20 minutes.

In this exercise you will create a view showing the number of rental by customer.

  1. Create a view to read:
    • Customer first name, last name and id
    • Number of rental that customer has made (as rental_count)
  2. Write a select statement that shows numbers of rental rounded to the nearest 10, with customer count that have made those number of rental.
  3. Hint: There will be some grouping involved.

The SELECT Statement Options

When you create a SELECT statement, your SELECT clause can include one or more options that are specified before the select list. The options define how a SELECT statement is processed and, for the most part, how it applies to the statement as a whole, rather to the specific data returned. As the following syntax shows, you can include a number of options in a SELECT statement:

To specify an option in a SELECT statement, you must add it after the SELECT keyword, as shown in the following SELECT statement:

<select option>::=
{ALL | DISTINCT | DISTINCTROW}
| HIGH_PRIORITY
| {SQL_BIG_RESULT | SQL_SMALL_RESULT}
| SQL_BUFFER_RESULT
| {SQL_CACHE | SQL_NO_CACHE}
| STRAIGHT_JOIN

The following table describes each of the options that you can include in a SELECT statement.

Option Description
ALL | DISTINCT | DISTINCTROW The ALL option specifies that a query should return all rows, even if there are duplicate rows. The DISTINCT and DISTINCTROW options, which have the same meaning in MySQL, specify that duplicate rows should not be included in the result set.
HIGH_PRIORITY The HIGH_PRIORITY option prioritizes the SELECTstatement over statements that write data to the target table, typically used for SELECT statements that execute fast.
SQL_BIG_RESULT | SQL_SMALL_RESULT The SQL_BIG_RESULT option informs the MySQL optimizer that the result set will include a large number of rows, which helps the optimizer to process the query more efficiently. On the other hand, the SQL_SMALL_RESULToption indicates that the result set will be smaller.
SQL_BUFFER_RESULT The SQL_BUFFER_RESULT option causes the query results to be placed in a temporary table for faster release of any table locks especially for large result sets.
SQL_CACHE | SQL_NO_CACHE These two options allow for some control over caching of result sets from queries. The SQL_CACHE option will cache the query results if the cache is operating in demand mode. The SQL_NO_CACHE option tells MySQL not to cache the query results.
STRAIGHT_JOIN The STRAIGHT_JOIN option instructs the optimizer to join the tables in the order specified in the FROM clause, useful when the optimizer is not joining the tables efficiently.

This statement uses the ALL to include all rows:

Code Sample:

SELECT ALL rating, category_name FROM film_detail
ORDER BY 1, 2;

There are several duplicate rows in the result set. We will use DISTINCT option to see just how many duplicates we do have:

Code Sample:

SELECT DISTINCT rating, category_name 
FROM film_detail;
Code Explanation

Wow! The new result set is less than 1/10th of the original result set !.

You can also specify multiple options in your SELECT clause, as the following example shows:

SELECT DISTINCT HIGH_PRIORITY rating, category_name FROM film_detail;

Code Sample:

SELECT DISTINCT HIGH_PRIORITY rating, category_name 
FROM film_detail;
Code Explanation

The HIGH_PRIORITY option only impacts queuing and has no impact on the values returned.

Subqueries

Subqueries are queries embedded in queries, also called nested SELECT queries. They are used to retrieve data from one table based on data in another table. They generally are used when tables have some kind of relationship, many times when a JOIN is not very intuitive.

For example, a subquery may be used on rental table to read customers living in a certain city.

SubQuery Variants

There are many versions of formulating subSELECTs in MySQL. The following list gives the most important variants along with useful examples.

SELECT ... WHERE col = [ANY/ALL] (SELECT ...)

With this variant, the second SELECT query must return a single value (one row and one column). This value is used for the comparison col= ... (There are additional comparison operators available, such as col > ..., col <=..., col <> ....) A comparison can be modified using the keyword ANY, its synonym SOME, or ALL. In this case, the second SELECT query can return more than one value. ANY/SOME means that all suitable values should be considered. The total query might then return several results. The formulation col = ANY... means the same as col IN ... (see the next variant).

The effect of ALL is not so obvious: The expression comparison operator ALL has the value TRUE if the comparison is true for all the results of the second SELECT query or if the second SELECT query returns no result.

Code Sample:

SELECT title, rating, category_name
FROM film_detail
WHERE film_id = ANY (SELECT i.film_id
FROM rental r JOIN inventory i USING (inventory_id)
WHERE r.rental_date BETWEEN '20050701' AND '20050715'
AND r.customer_id = 101 );
Code Explanation

List films where any film has been rented in a given period.

A subquery on rental/inventory is used to filter films.

SELECT ... WHERE col [NOT] IN (SELECT ...)

In this variant the second SELECT query may return an entire list of individual values. This list is then processed in the form SELECT ... WHERE col IN (n1, n2, n3). In place of IN, one may use NOT IN.

Code Sample:

SELECT last_name, first_name, email
FROM customer
WHERE customer_id IN (SELECT customer_id
FROM rental
WHERE rental_date BETWEEN '20050701' AND '20050715'
AND inventory_id BETWEEN 2030 AND 2050 );
Code Explanation

A subquery on rental is used to filter customers.

SELECT ROW(value1, value2, ...) = [ANY] (SELECT col1, col2, ...)

This query tests whether a record exists satisfying certain specified criteria. The possible results are 1 (true) and NULL (false). The main difference between this variant and the two previous ones is that the comparison criterion is not a single value, but a group of values.

MySQL compares the record ROW(value1, value2, ...) with the result of the second SELECT command, which must return precisely one data record. If the result record is the same as the ROW record, then the entire query returns 1. Otherwise, it returns NULL.

If the optional keyword ANY or its synonym SOME is used, the second SELECT query may return more than one result. If at least one of them is identical to the ROW record, then the entire query returns the result 1.

Code Sample:

select title,rating,category_name from film_detail where rating = 'PG' AND category_name = 'Foreign';

SELECT ROW('PG', 'Foreign') = ANY (SELECT rating, category_name FROM film_detail) 'Has PG/Foreign';
Code Explanation

First query is a simple select, and second uses ROW to check existence.

SELECT ... WHERE [NOT] EXISTS (SELECT ...)

In this variant, the second SELECT query is executed for every record found in the first SELECT query. Only if this second query yields a result (that is, at least one record) does the record from the first SELECT query remain in the result list. In this variant, the negation operation NOT may be prefixed.

EXISTS constructions are generally useful only if the records of the two SELECT commands are joined with a WHERE condition (as with a JOIN operation).

Code Sample:

SELECT f.title, f.rating, f.category_name
FROM film_detail f
WHERE EXISTS (SELECT 1
FROM rental r JOIN inventory i USING (inventory_id)
WHERE r.rental_date BETWEEN '20050701' AND '20050715'
AND r.customer_id = 101
AND i.film_id = f.film_id );
Code Explanation

List films where any film has been rented in a given period.

A subquery on rental/inventory is used to filter films.

SELECT ... FROM (SELECT ...) AS name WHERE ...

In this variant, which is seldom used in practice, first the SELECT command in parentheses is executed. It returns a table, which serves as the basis of the outer SELECT command. In other words, the outer SELECT command does not access a preexisting table, as is usually the case, but a table that is itself the result of a SELECT command. Such tables are called derived tables. The SQL syntax prescribes that such a table must be named using AS name.

Code Sample:

SELECT f.title, f.rating, f.category_name
FROM film_detail f, (SELECT i.film_id
FROM rental r JOIN inventory i USING (inventory_id)
WHERE r.rental_date BETWEEN '20050701' AND '20050715'
AND r.customer_id = 101) sq
WHERE sq.film_id = f.film_id;
Code Explanation

An inner subquery is used like a table to read and match data.

Nesting JOINS

Often SELECT commands may be nested within one another. Largely a matter of preference, such nesting may gets unwieldy. Sub-SELECT commands can also be used within WHERE conditions of UPDATE and DELETE commands for restrict which records may be changed or deleted.

Many times, nested subqueries can be replaced with JOINs and filter conditions.

Code Sample:

SELECT title, rating, category_name
FROM film_detail
WHERE film_id = ANY (SELECT i.film_id
FROM inventory i
WHERE inventory_id IN ( SELECT r.inventory_id FROM rental r
WHERE r.rental_date BETWEEN '20050701' AND '20050715'
AND r.customer_id = 101 ));
Code Explanation
  • An inner subquery is using another inner subquery.
  • This is really a variant on a previous JOIN example. Can you find it?

Tip: Read an interesting article on subSELECT syntax at http://dev.mysql.com/tech-resources/articles/4.1/subqueries.html. SubSELECTs are also extensively discussed in the MySQL documentation: http://dev.mysql.com/doc/mysql/en/subqueries.html.

SubSELECT Limitations

  • MySQL is not yet capable of evaluating LIMIT in subSELECT commands; for example, SELECT.. WHERE ... IN (SELECT ... LIMIT 10). If you try to do this, you will receive the following error message:
    ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT', 'IN/ALL', 'ANY/SOME
    subquery'
    .
  • If you use subSELECTs to alter records (DELETE, UPDATE), the table to be updated and the subSELECT table must be different.
  • In many cases, subSELECTs are executed inefficiently. The reason is that subSELECTs for MySQL seem to be much more difficult to optimize than ordinary SELECT command with JOINs. In speed- critical applications it would be a good idea to use equivalent SQL commands without subSELECTs.

Code Sample:

SELECT title FROM film_detail
WHERE language_id NOT IN
(SELECT language_id FROM language WHERE language_id BETWEEN 1 AND 3 LIMIT 2);

SELECT title FROM film_detail
WHERE language_id NOT IN
(SELECT ANY language_id FROM language WHERE language_id BETWEEN 1 AND 3);

UPDATE film_detail SET rating = 'PG-13'
WHERE film_id IN (SELECT film_id FROM film_detail WHERE rating = 'PG' AND category_name = 'Foreign');
Code Explanation

The first subquery fails as it uses LIMIT.

The second subquery fails as it uses ANY.

The third update fails as the subquery refers to table being updated.

SubSelects as Join

It is often possible to reformulate a subSELECT command as a JOIN query. However, to do so is not particularly intuitive.

The following two equivalent SELECT commands provide an example:

Code Sample:

SELECT title FROM film_detail
WHERE language_id NOT IN
(SELECT language_id FROM language WHERE language_id BETWEEN 1 AND 3);

SELECT title
FROM film_detail f JOIN language l USING (language_id)
WHERE NOT l.language_id BETWEEN 1 AND 3;

Code Explanation

First query is a sub-select, and second uses a Join.

In more complex cases involving equivalents of subSELECTs, it may be necessary to use a temporary table with intermediate results.

Exercise: Subqueries

Duration: 10 to 20 minutes.

In this exercise, you will practice writing subqueries.

  1. Using sub-queries, list the film title, year of release and rating for all films in 'Japanese' or 'Mandarin' languages.
  2. Using sub-queries, list the film title, rental_rate and rating for all films in 'Music' category.

Advanced Queries Conclusion

As the lesson has demonstrated, the SELECT statement can contain many components, allowing you to create statements as simple or as complex as necessary to retrieve specific data from the tables in your MySQL database.

You also learned the purpose and benefits of views and how to create and drop them.

To continue to learn MySQL go to the top of this page and click on the next lesson in this MySQL Tutorial's Table of Contents.
 


mysql tools

Selection options GUI tool for mysql for purchase.

'mysql.proc' doesn't exist fixed problem

_

Table 'mysql.proc' doesn't exist. Fix error 1146 Table doesn't exist here...