mq6

So far we've created a database, added two tables, and inserted data into both tables. Now we're going to look at how to retreive that data. But not just retrieve the data, but to query the database for it.

SQL stands for Structured Query Language. It's the Query bit that makes SQL so powerful. SQL is a language that enables you to run queries against your database. It allows you to query the database for the data that you want.

The SQL SELECT Statement

When it comes to querying your MySQL database, the SQL SELECT statement makes it all possible. This is likely to be your most frequently used SQL statement when working with MySQL. The SELECT statement allows you to describe to MySQL exactly what data you want it to retrieve.

Consider the following SQL statement:

SELECT * FROM Fruit;

Here's the result:

This is the SELECT statement at its simplest. The above SQL statement retrieves all records from the Fruittable.

The asterisk (*) tells MySQL to return all columns. This saves us time and effort. Without this we'd need to write the names of all columns we'd like to have returned.

Having said that, you can also tell MySQL to return only those columns that you want to return. This is done by naming only those columns that you'd like to have returned. Like this:

SELECT FruitId, FruitName 
FROM Fruit;

Here's the result:

The above SQL statement selects the FruitId and FruitName columns from the Fruit table.

Doing this can reduce clutter so that you only see those columns that you're interested in. It can also boost performance, because MySQL (and any application you use) doesn't need to use valuable resources to return unnecessary data.

Again, this query retreives all records from the table — MySQL will return all records unless otherwise specified.

The WHERE Clause

You can add the WHERE clause to narrow the result set down to only those records that you're interested in. Like this:

SELECT * FROM Fruit
WHERE UnitId = 1;

Result:

The above query returns all records from the Fruit table where the UnitId column has a value of 1.

Subqueries — Nested SELECT Statements

What if we didn't know the UnitId? What if we only knew to look for those records with a unit name of Piece?

Easy! We could rewrite the above example to use a nested SELECT statement (otherwise known as a subquery) that queries another table (the Units table). Doing this will enable us to use the actual unit name (rather than its ID) because the second table contains this in the UnitName field:

SELECT * FROM Fruit
WHERE UnitId = 
 (SELECT UnitId 
    FROM Units 
    WHERE UnitName = 'Piece');

Result:

Here we use a nested SELECT statement (i.e. a SELECT statement within a SELECT statement) to query the Units table for the UnitId of the record that contains Piece as its UnitName value. We can do this because Fruit.UnitId column is a foreign key to the Units.UnitId column.

Here are more examples of subqueries if you're interested.

Using an SQL JOIN

Taking it a step further, we could rewrite our nested SELECT statement into an INNER JOIN.

In SQL, a JOIN enables you to query multiple tables that share data. In our case, both tables share the UnitId so you could say that they are "joined" by this field.

There are different types of joins in SQL, however, we're mainly interested in the INNER JOIN for now.

The INNER JOIN syntax goes like this:

SELECT * FROM table_name_1
INNER JOIN table_name_2
ON table_name_1.column_name = table_name_2.column_name

So we could rewrite our subquery from the previous example to the following:

SELECT Fruit.* FROM Fruit
INNER JOIN Units
ON Fruit.UnitId = Units.UnitId
WHERE Units.UnitName = 'Piece';

Result:

We specified Fruit.* instead of just * because we only wanted to return all columns from the Fruittable. If we'd used *, the query would've returned all columns from both tables.

Also check out the LEFT JOIN and RIGHT JOIN to see how you may get different data depending on the join type.

Subquery vs JOIN?

Now that you've seen two methods for achieving the same result, you're probably wondering which one is better?

Subqueries tend to be more readable (and perhaps easier to comprehend), which can make it easier for beginners to grasp.

However, many SQL programmers find JOINs more efficient and perform better. If you encounter performance issues with your queries or an application try converting any subqueries into JOINs or vice versa (in some cases a sub-query could perform better).

Also, there may be cases where a subquery is your only option, so this is also a consideration.

More Operators

Our queries so far have all contained an equals sign (=). This is called an operator. More specifically it's a comparison operator as it compares one expression with another.

There are many more operators that you can use in your queries. These can assist greatly in narrowing down the result set to only those records that you require. It's not uncommon for a database to contain millions of records. Even if you only have thousands of records, trying to find one record (or even just a handful) amongst thousands would be a very daunting task if you didn't have these operators at your disposal.

Here are some of the more commonly used SQL operators.

The > Operator

You can use the > operator to select data that is greater than a given value.

SELECT * FROM Fruit
WHERE Inventory > 10;

The < Operator

You can use the < operator to select data that is less than a given value.

SELECT * FROM Fruit
WHERE Inventory < 10;

The <> Operator

You can use the <> operator to select data that is both less than and greater than a given value.

SELECT * FROM Fruit
WHERE Inventory <> 10;

The >= Operator

You can use the >= operator to select data that is greater than or equal to a given value.

SELECT * FROM Fruit
WHERE Inventory >= 10;

The <= Operator

You can use the <= operator to select data that is less than or equal to a given value.

SELECT * FROM Fruit
WHERE Inventory <= 10;

The AND Operator

You can add an AND operator to the WHERE clause in order to limit your selection to only those records that meet two conditions (or more if you include more AND operators).

Here's an example:

SELECT * FROM Fruit
WHERE Inventory > 10 
AND DateEntered > '2015-01-15';

The OR Operator

You can use an OR operator to broaden your selection to more than one criteria. As the name suggests, the OR clause lets you select data where the criteria is either this OR that. So the AND operator limits your selection and the OR operator broadens it.

Here's an example:

SELECT * FROM Fruit
WHERE UnitId = 1 OR UnitId = 2;

The BETWEEN Operator

Use the BETWEEN operator to select data that is between two given values.

SELECT * FROM Fruit
WHERE DateEntered 
BETWEEN '2015-01-25' AND '2015-02-25';

The NOT Operator

Use the NOT operator to select data that is not equivalent to a given condition.

SELECT * FROM Fruit
WHERE NOT (FruitName = 'Apple');