Home >> FAQs/Tutorials >> MySQL Tutorials

MySQL FAQs - SQL SELECT Statements with JOIN and Subqueries

By: FYIcenter.com

Part:   1   2  3  4  5 

A collection of 16 FAQs on MySQL SELECT statements with JOIN and subqueries. Clear answers are provided with tutorial exercises on joining multiple tables with inner and outer joins; using subqueries with IN, EXISTS, and FROM clauses; using UNION operations. Topics included in this FAQ are:

1. How To Join Two Tables in a Single Query?
2. How To Write a Query with an Inner Join?
3. How To Define and Use Table Alias Names?
4. How To Write a Query with a Left Outer Join?
5. How To Write a Query with a Right Outer Join?
6. How To Write a Query with a Full Outer Join?
7. How To Write an Inner Join with the WHERE Clause?
8. How To Name Query Output Columns?
9. What Is a Subquery?
10. How To Use Subqueries with the IN Operator?
11. How To Use Subqueries with the EXISTS Operator?
12. How To Use Subqueries in the FROM clause?
13. How To Count Groups Returned with the GROUP BY Clause?
14. How To Return Top 5 Rows?
15. How To Return the Second 5 Rows?
16. How To Use UNION to Merge Outputs from Two Queries Together?

Please note that all answers and tutorials are based on MySQL 5.0. Tutorial exercises should be executed with "mysql" or other MySQL client programs. It is also assumed that you have a MySQL user account and a predefined database with enough privileges.

Some sample scripts requires database tables created by other samples in the beginning of the collection.

For questions on SELECT statement basics and GROUP BY, see the previous FAQ collection.

How To Join Two Tables in a Single Query?

Two tables can be joined together in a query in 4 ways:

• Inner Join: Returns only rows from both tables that satisfy the join condition.
• Left Outer Join: Returns rows from both tables that satisfy the join condition, and the rest of rows from the first (left) table.
• Right Outer Join: Returns rows from both tables that satisfy the join condition, and the rest of rows from the second (right) table.
• Full Outer Join: Returns rows from both tables that satisfy the join condition, the rest of rows from the first (left) table, and the rest of rows from the second (right) table.

How To Write a Query with an Inner Join?

If you want to query from two tables with an inner join, you can use the INNER JOIN ... ON clause in the FROM clause. The tutorial exercise below creates another testing table and returns output with an inner join from two tables: fyi_links and fyi.rates. The join condition is that the id in the fyi_links table equals to the id in the fyi_rates table:

mysql> CREATE TABLE fyi_rates (id INTEGER, 
   comment VARCHAR(16));
   
mysql> INSERT INTO fyi_rates VALUES (101, 'The best');
mysql> INSERT INTO fyi_rates VALUES (102, 'Well done');
mysql> INSERT INTO fyi_rates VALUES (103, 'Thumbs up');
mysql> INSERT INTO fyi_rates VALUES (204, 'Number 1');
mysql> INSERT INTO fyi_rates VALUES (205, 'Not bad');
mysql> INSERT INTO fyi_rates VALUES (206, 'Good job');
mysql> INSERT INTO fyi_rates VALUES (207, 'Nice tool');

mysql> SELECT fyi_links.id, fyi_links.url, 
   fyi_rates.comment FROM fyi_links
   INNER JOIN fyi_rates ON fyi_links.id = fyi_rates.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.49 sec)

Note that when multiple tables are used in a query, column names need to be prefixed with table names in case the same column name is used in both tables.

Note also that there are a number of rows from both tables did not return in the output because they did meet the join condition.

(Continued on next part...)

Part:   1   2  3  4  5