MySQL Queries
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 Fruit table.
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 SELECTstatement) 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.
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 Fruit table. 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');
