Home >> FAQs/Tutorials >> MySQL Tutorials >> Index

MySQL FAQs - SQL SELECT Statements with JOIN and Subqueries

By: FYIcenter.com

Part:   1  2  3   4  5 

(Continued from previous part...)

How To Write an Inner Join with the WHERE Clause?

If you don't want to use the INNER JOIN ... ON clause to write an inner join, you can put the join condition in the WHERE clause as shown in the following query example:

mysql> SELECT l.id, l.url, r.comment 
   FROM fyi_links l, fyi_rates r WHERE l.id = r.id;
+-----+-------------------+-----------+
| id  | url               | comment   |
+-----+-------------------+-----------+
| 101 | dev.fyicenter.com | The best  |
| 102 | dba.fyicenter.com | Well done |
| 103 | sqa.fyicenter.com | Thumbs up |
+-----+-------------------+-----------+
3 rows in set (0.00 sec)

How To Name Query Output Columns?

Each column in the query output has a default name. If you don't like the default name, you can specify a new name for any column in the query output by using the AS clause. The following statement shows you a good example:

mysql> SELECT tag AS Category, YEAR(created) AS Year, 
   COUNT(*) AS Counts FROM fyi_links 
   GROUP BY tag, YEAR(created) ORDER BY COUNT(*) DESC;
+----------+------+--------+
| Category | Year | Counts |
+----------+------+--------+
| DBA      | 2006 |      2 |
| DEV      | 2006 |      1 |
| SQA      | 2006 |      1 |
| DBA      | 2005 |      1 |
| DEV      | 2004 |      1 |
| SQA      | 2003 |      1 |
+----------+------+--------+
6 rows in set (0.00 sec)

What Is a Subquery?

A subquery is a SELECT statement used as part of the selection criteria of the main SELECT statement. The subquery specified in the WHERE clause will be evaluated repeated on each row of the selection base table. The output of the subquery will be used in the final evaluation of the criteria. Usually, subqueries are used in the following Boolean operations:

• "expression IN (subquery)"
• "expression NOT IN (subquery)"
• "EXISTS (subquery)"
• "NOT EXISTS (subquery)"

How To Use Subqueries with the IN Operator?

A subquery can be used with the IN operator as "expression IN (subquery)". The subquery should return a single column with one or more rows to form a list of values to be used by the IN operation. The following tutorial exercise shows you how to use a subquery with the IN operator. It returns all links with ids in the fyi_rates table.

mysql> SELECT id, url, tag, YEAR(created) As year 
   FROM fyi_links WHERE id IN (SELECT id FROM fyi_rates);
+-----+-------------------+------+------+
| id  | url               | tag  | year |
+-----+-------------------+------+------+
| 101 | dev.fyicenter.com | DEV  | 2006 |
| 102 | dba.fyicenter.com | DBA  | 2006 |
| 103 | sqa.fyicenter.com | SQA  | 2006 |
+-----+-------------------+------+------+
3 rows in set (0.06 sec)

How To Use Subqueries with the EXISTS Operator?

A subquery can be used with the EXISTS operator as "EXISTS (subquery)", which returns true if the subquery returns one or more rows. The following statement is a good example of "EXISTS (subquery)". It returns rows from fyi_links table that there are rows existing in the fyi_rates table with the same id.

mysql> SELECT id, url, tag, YEAR(created) As year 
   FROM fyi_links WHERE EXISTS (
   SELECT * FROM fyi_rates 
   WHERE fyi_rates.id = fyi_links.id);
+-----+-------------------+------+------+
| id  | url               | tag  | year |
+-----+-------------------+------+------+
| 101 | dev.fyicenter.com | DEV  | 2006 |
| 102 | dba.fyicenter.com | DBA  | 2006 |
| 103 | sqa.fyicenter.com | SQA  | 2006 |
+-----+-------------------+------+------+
3 rows in set (0.00 sec)

(Continued on next part...)

Part:   1  2  3   4  5