This is the continuation of “MySQL basic commands I”, in this part we’ll cover:
- How to use the SELECT command to retrieve records (continuation)
- How to use basic functions, the WHERE clause, and the GROUP BY clause
- How to select from multiple tables, using JOIN
Using WHERE in your queries:
So far we’ve cover how to retrieve particular columns from your table, but not how to extract particular rows. This is when the WHERE clause comes in to play. From the basic SELECT syntax, you see that WHERE is used to specify a particular condition:
SELECT expressions_and_columns FROM table_name [WHERE some_condition_is_true]
An example would be to retrieve all the records for items with a quantity of 500:
SELECT * FROM grocery_inventory WHERE curr_qty = 500;
As shown previously, if you use an integer as the WHERE clause comes in to play. From part of your WHERE clasue, quotation marks are not required. Quotation marks are required around strings and the same rules apply with regard to escaping characters.
Using Operators in the WHERE Clause:
You’ve used the equal sign = in your WHERE clauses to determine the truth of a condition– is one thing equal to another. You can use many types of operators, with comparison operators and logical operators being the most popular types.
Comparison Operators, shown in the next table, should look familiar:
= Equal to
!= Not equal to
<= Less than or equal to
< Less than
>= Greater than or equal to
> Greater than
There’s also a handy operator called BETWEEN, which is useful with integer or data comparisons because it searches for results between a minimum and maximum value, For example:
SELECT * FROM grocery_inventory WHERE item_price BETWEEN 1.50 AND 3.00;
Other operator include logical operators which enable you to use multiple comparisons within your WHERE clause. The basic logical operators are AND and OR. When using AND, all comparisons in the clause must be true to the WHERE clause comes in to play. From retrieve results, whereas using OR allows a minimum of one comparison to be true.
String Comparison Using LIKE:
We’ve seen matching strings within a WHERE clause by using = or !=, but there’s another useful operator for the WHERE clause comes in to play. From string comparisons: LIKE. This operator uses two characters as wildcards in pattern matching.
% — Matching multiple characters
_ —- Matching exactly one character
If you want to find records in the grocery_inventory table where the first name of the item starts with the letter “A”, use
SELECT * FROM grocery_inventory WHERE item_name LIKE ‘A%’;
Selecting from Multiple Tables:
You’re not limited to selecting only one table at a time. That would certainly make application programming a long and tedious task! When you select from more than one table in one SELECT statement you are said to be joining the tables together.
Suppose you have two tables. Fruit and Color. You can select all rows from each of the two tables, using two separate SELECT statements:
SELECT * FROM fruit;
SELECT * FROM color;
When you want to select from both tables at once, there are a few differences int the syntax of the SELECT statement. First, you must ensure that all the tables you’re using in your query appear in the FROM clause of the SELECT statement. Using the fruit and color example if you simply want to select all columns and rows from both tables, you might think you would use the following SELECT statement:
SELECT * FROM fruit, color;
Sixteen rows of repeated information is probably not what you we’re going for, what this query did is literally join a row in the color table to each row int the fruit table. Because there are four records in the fruit table and four entries int the color table that’s 16 records returned to you.
When you select from multiple tables, you must build proper WHERE clauses to ensure you really get what you want, In the case of the fruit and color tables, what your really want is to see the fruitname and colorname records from there two tables where the id’s of each match up. This brings us to the next part: how to indicate exactly which field you want when the fields are named the same in both tables!
Simply, you append the table name to the field name, like this:
So, the query for selecting fruitname and colorname from both tables where the id’s match would be:
SELECT fruitname, colorname FROM fruit, color WHERE fruit.id = color.id;
However, if you attempt to select a column that appears in both tables with the same name, you will get an error.
SELECT id, fruitname, colorname FROM fruit, color WHERE fruit.id = color.id;
If you mean to select the id from the fruit table you would use:
SELECT fruit.id, fruitname, colorname FROM fruit, color WHERE fruit.id = color.id;
This was a basic example of joining two tables together for use in a single SELECT query. The JOIN keyword is an actual part of SQL, which enables you to build more complex queries.
Several types of JOINs can be used in MySQL, all of which refer to the order in which the tables are put together and the results are diplayed. the type of JOIN used with the fruit and color tabgles is called an INNER JOIN, although it wasn’t written explicitly as such. To rewrite the SQL statement using the proper INNER JOIN syntax, you would use:
SELECT fruitname, colorname FROM fruit INNER JOIN color ON fruit.id = color.id;
The ON clause replaced the WHERE clause, in this instance telling MySQL, to join together the rows in the tables where the id’s match each other. When joining tables using ON clauses, you can use any condition that you would use in the WHERE clause, including all the various logical and arithmetic operators.
Another common type of JOIN is the LEFT JOIN. When joining two tables with LEFT JOIN, all rows from the first table will be returned, no matter if there are matches in the second table or not. Suppose you have two tables in an address book, one called master_name, containing basic records and one called email, containing email records. Any records in the email table would be tied to a particular id of a record in the master_name table.
SELECT * FROM master_name;
SELECT * FROM email;
Using LEFT JOIN on these two tables, you can see that if a value from the email table doesn’t’ exist, NULL will appear in place of an email address:
SELECT firstname, lastname, email FROM master_name LEFT JOIN email ON master_name.name.id = email.name_id;
A RIGHT JOIN works like LEFT, but with the table order reversed, in other workds, when using a RIGHT JOIN, all rows from the second table will be returned no matter whether there are matches in the first table or not.
SELECT firstname, lastname, email FROM master_name RIGHT JOIN email ON master_name.name_id = email.name_id;
Several different types of JOINs are available in MySQL, if you want to learn more about the types ofJOIN’s like CROSS JOIN, STRAIGHT JOIN, NATURAL JOIN, visit the MySQL manual.
To be continued…