Quite possibly the most important function you'll perform in your dynamic web applications is to query existing data in the database. Many web developers spend a great deal of time concentrating on the design layout of the web pages, but the real heart of the application is the behind-the-scenes SQL used to query data to produce the website content. If this code is inefficient, it can cause huge performance problems, and possibly even make the web application virtually useless to customers — no matter how fancy the web pages look.
The basic SELECT format
The basic format for the SELECT statement seems simple enough:
SELECT fieldlist FROM table
The fieldlist parameter specifies the data fields that should appear in the output from the table you specify. The fieldlist can be a comma-separated list of specific data fields in the table, or the wildcard character (the asterisk) to specify all data fields, as shown in the SELECT example I use earlier in this chapter:
SELECT * FROM employees;
This statement returns all the data field values for all the data records contained in the Employees table. If that's all you need for your application, you don’t need to know anything more about the SELECT statement (lucky you)!
However, more than likely, you’ll need to customize just what data fields (and data records) need to appear in the output. That’s where things start getting complicated. The following sections show some more features that you may need to use in your SELECT statements.
Sorting output data
The output from a SELECT statement is called a result set. The result set contains only the data fields specified in the SELECT statement. The result set is only temporary and, by default, is not stored in any tables in the database.
By default, the data records displayed in the result set are not displayed in any particular order. As records are added or removed from the table, MySQL may place new data records anywhere within the table order. Even if you enter data in a particular order using INSERT statements, there is still no guarantee that the records will display in the same order in the result set.
If you need to specify the order in which the data records appear in your output, you must add the ORDER BY clause to the SELECT statement:
> SELECT employeeid, lastname, firstname FROM employees ORDER BY firstname;
+------------+----------+-----------+
| employeeid | lastname | firstname |
+------------+----------+-----------+
| 124 | Blum | Barbara |
| 126 | Blum | Jessica |
| 125 | Blum | Katie |
| 123 | Blum | Rich |
+------------+----------+-----------+
4 rows in set (0.00 sec)
>
In this example, only the data fields specified in the SELECT statement are displayed, ordered by the firstname data field. The default order used by the ORDER BY clause is ascending order, based on the data type and collation you select when creating the table. You can change the order to descending by adding the DESC keyword at the end of the ORDER BY clause:
ORDER BY firstname DESC;
This gives you complete control over how the data records appear in the result set output.
Filtering output data
By default, the SELECT statement places all the data records in the table in the result set output. The power of the database query comes from displaying only a subset of the data that meets a specific condition.
You add the WHERE clause to the SELECT statement to determine which data records to display in the result set output. Now we're getting to the heart of the SELECT statement!
For example, you can check for all the employees who work in the department identified by departmentcode 5 by using the following query:
MariaDB [dbtest1]> SELECT * FROM employees WHERE departmentcode = 5;
+----+-------+-------+----------------+------------+--------+------------+
| id | lname | fname | departmentcode | startdate | salary | birthdate |
+----+-------+-------+----------------+------------+--------+------------+
| 123| Blum | Rich | 5 | 2020-01-02 | 10000 | 2000-05-01 |
| 125| Blum | Katie | 5 | 2020-02-25 | 14000 | 2004-01-01 |
+----+-------+-------+----------------+------------+--------+------------+
2 rows in set (0.00 sec)
MariaDB [dbtest1]>
The result set only contains the data records from the table that match the WHERE clause condition you specified. In this example, the data field was an integer type, but if the data field you use is a text or date value, you must place quotes around the value to delineate the start and end of the value:
SELECT * FROM employees WHERE startdate < "2020-03-01";
In the WHERE clause condition, the collation you define for the data field is important. MySQL evaluates the specified condition based on the collation defined. If you use a case-insensitive collation, MySQL can't tell the difference between the values Rich and rich. Be very careful in selecting the collation you use for tables, because that plays an important role in just how your application can handle the data contained in the tables.
More advanced queries
Now that you've seen the basics (and the power) of the SELECT statement, let’s dive into some more complex topics. The following sections help add to your SELECT querying skills by showing you how to do some pretty complex searches in your database!
Querying from multiple tables
In a relational database, data is split into several tables in an attempt to keep data duplication to a minimum. In Chapter 3 of this minibook, I show you how to apply the second normal form rule of database design to create separate Customers and Orders tables so that the customer information didn't need to be duplicated for every order data record. Although this helps reduce data redundancy, it produces a small problem for your application queries.
When your application needs to generate a report for an order, it most likely will need the customer’s address information to place on the report. That means now your program needs to retrieve the order information from the Orders table, and the customer information from the Customers table.
You can do that with two separate queries:
- Query the Orders table with the
orderid value to retrieve the customerid.
- Query the Customers table with the
customerid to retrieve the customer address information for that order.
However, the two separate queries do take some extra processing time, both in your PHP application code and in the MySQL server. A more efficient way of retrieving that information is to submit a single SELECT statement that retrieves the data from both tables.
To query data from multiple tables in a single SELECT statement, you must specify both tables in the FROM clause. Also, because you're referencing data fields from both tables in the data field list, you must indicate which table each data field comes from. That looks like this:
MariaDB [dbtest1]> SELECT orders.orderid, customers.name, customers.address
-> FROM orders, customers
-> WHERE orderid = 1000 AND orders.customerid = customers.customerid;
+---------+------------+-------------------------+
| orderid | name | address |
+---------+------------+-------------------------+
| 1000 | Acme Paper | 134 Main St.; Miami, FL |
+---------+------------+-------------------------+
1 row in set (0.00 sec)
MariaDB [dbtest1]>
As you can see from this example, it doesn’t take long for a seemingly simple SELECT statement to get complex! Let’s walk through just what this statement does.
The first line in the query defines the data fields you want to see in the result set output. Because you’re using data fields from two tables, you must precede each data field name with the table it comes from.
In the second line, you have to define which tables the data fields come from in the FROM clause. You can list the tables in any order here.
Finally, in the WHERE clause, you have to define the condition that filters out the records you want to display. In this example, there are two conditions that must be met:
- You need the Orders table data record that meets the specific
orderid value you're looking for.
- You need the Customers table data record that matches the
customerid value for that specific order.
You use the logical AND operator to combine the two conditions. The result set contains the data record values that meets both of those conditions.
Using joins
In the previous example, you had to write a lot of code in the WHERE clause to match the appropriate data record from the Customers table to the Orders table data record information. In a relational database, this is a common thing to do. To help programmers, the SQL designers came up with an alternative way to perform this function.
A database join matches related data records in relational database tables without your having to perform all the associated checks in your code. Here's the format for using the join in a SELECT statement:
SELECT fieldlist FROM table1 jointtype JOIN table2 ON condition
The fieldlist parameter lists the data fields from the tables to display in the output as usual. The table1 and table2 parameters define the two tables to perform the join on. The jointype parameter determines the type of join for MySQL to perform. There are three types of joins available in MySQL:
INNER JOIN: Only displays data records found in both tables.LEFT JOIN: Displays all records in table1 and the matching data records in table2.RIGHT JOIN: Displays all records in table2 and the matching data records in table1.
The LEFT and RIGHT join types are also commonly referred to as outer joins. The order in which you specify the tables and the join type that you use are very important to the join operation.
Finally, the ON condition clause defines the data field relation to use for the join operation.
It's common practice to use the same data field name for data fields in separate tables that contain the same information (such as the customerid data field in the Customers and Orders tables). You can add the NATURAL keyword before the join type to tell MySQL to join using the common data field name. Here's an example of querying the customer information for all the orders using a NATURAL INNER JOIN:
MariaDB [dbtest1]> SELECT orders.orderid, customers.name, customers.address
-> FROM orders NATURAL INNER JOIN customers;
+---------+---------------+------------------------------+
| orderid | name | address |
+---------+---------------+------------------------------+
| 1000 | Acme Paper | 134 Main St.; Miami, FL |
| 1001 | Acme Paper | 134 Main St.; Miami, FL |
| 1002 | Acme Machines | 264 Oak St.; Los Angeles, CA |
+---------+---------------+------------------------------+
3 rows in set (0.00 sec)
MariaDB [dbtest1]>
Now that’s a lot less typing to mess with! The result set shows all the data records in the Orders table that have matching customerid data records in the Customers table.
Another way of doing this is to add the USING clause to a JOIN statement:
MariaDB [dbtest1]> SELECT orders.orderid, customers.name, customers.address
-> FROM orders LEFT JOIN customers USING (customerid);
+---------+---------------+------------------------------+
| orderid | name | address |
+---------+---------------+------------------------------+
| 1000 | Acme Paper | 134 Main St.; Miami, FL |
| 1001 | Acme Paper | 134 Main St.; Miami, FL |
| 1002 | Acme Machines | 264 Oak St.; Los Angeles, CA |
+---------+---------------+------------------------------+
3 rows in set (0.00 sec)
MariaDB [dbtest1]>
The USING keyword works with the LEFT and RIGHT joins to specify the data field for the join operation.
Using joins the wrong way can cause severe performance issues on your MySQL server, especially when working with large amounts of data (joining all the data records in tables with millions of data records can take quite a long time). I strongly suggest testing out your SELECT statements first before coding them into your web application. That will help give you a feel for any performance issues that may occur. In some situations, it's better to submit multiple smaller SELECT statements than to submit a single complex SELECT statement.
Using aliases
Having to specify the table and data field names in SELECT statements can get somewhat cumbersome. To help out, you can use the table alias feature, which defines a name that represents the full table name within the SELECT statement. Here's the format for using aliases:
SELECT fieldlist FROM table AS alias
When you define an alias for a table, you can use the alias anywhere within the SELECT statement to reference the full table name. This is especially handy in the long WHERE clauses when you're working with multiple tables:
MariaDB [dbtest1]> SELECT t1.orderid, t2.name, t2.address
-> FROM orders as t1, customers as t2
-> WHERE t1.orderid = 1000 AND t1.customerid = t2.customerid;
+---------+------------+-------------------------+
| orderid | name | address |
+---------+------------+-------------------------+
| 1000 | Acme Paper | 134 Main St.; Miami, FL |
+---------+------------+-------------------------+
1 row in set (0.00 sec)
MariaDB [dbtest1]>
The t1 alias represents the Orders table, and the t2 alias represents the Customers table. Notice that you can use the aliases anywhere in the SELECT statement, even in the data field list!
Adding new data to your tables
In some MySQL documentation, you'll often see the terms column used to refer to a single data field and tuple used to refer to an entire data record. I’ll use the more generic terms data field and data record in this book.
