Available in Non-Essentials editions of Navicat for MySQL, PostgreSQL, SQLite, MariaDB, and Navicat Premium, the Query Builder is a tool for creating and editing queries visually. In Part 1, we used it to write a query to fetch a list of actors that appeared in movies released during a given year. Part 2 was all about field selection. Today's blog will provide an overview on adding WHERE criteria to a SELECT query using the Navicat Premium Query Builder.
About the Sakila Sample Database
As with parts 1 and 2, the queries that we'll be building here today will run against the Sakila sample database. It contains a number of tables themed around the film industry that cover everything from actors and film studios to video rental stores. Please refer to the Generating Reports on MySQL Data tutorial for instructions on downloading and installing the Sakila database.
Using the WHERE Clause
The WHERE clause is the section of a SELECT query that filters the results based on a set of criteria. It's useful in reducing the number of rows returned by specifying the subset of records that we're interested in. For instance, taking our query from part 1 that produced a list of actors that appeared in movies released during a given year, it still returned almost one thousand rows. One way to further limit the number of rows returned would be to include only certain actors that we wanted information on.
Add the following SQL to the Navicat Premium Query Editor and click the Query Builder button to display it in the Query Builder:
SELECT
film.title,
film.film_id,
film.release_year,
concat('$', film_list.price) AS price,
film_list.actors
FROM
film
INNER JOIN film_list ON film.film_id = film_list.FID
Beside the WHERE clause you'll see the label "<Click here to add conditions>". In the Query Builder, all labels within "<...>" brackets are clickable and open a context-specific list and/or editor. Clicking the "<Click here to add conditions>" label changes the text to the "<--> = <-->" expression. It's actually three different clickable regions:
- The left-hand field/expression: "<-->"
- The comparison operator: "="
- The right-hand field/expression: "<-->"
Let's proceed to fill out the expression from left to right, as we would in writing a query by hand.
We can search the actors field using a Like expression. Click on the "<-->" label to the left of the equals sign ("=") and select the film_list.actors item from the field list tab in the popup dialog (it's the last one):
Now click the equals sign ("="). That opens a list of comparison operators to choose from. Select the "Like" operator:
Next, we'll enter the actor that we're looking for. Click the "<-->" label to the right of the equals sign ("=") and enter "'%GENE HOPKINS%'" (without the double quotes) in the Edit tab:
With our WHERE criteria set, click the Query Builder's OK button to close the dialog. You'll see that the "WHERE film_list.actors LIKE '%GENE HOPKINS%'" line has been appended to the SELECT statement in the Query Editor.
Run the query and verify that all 22 rows list GENE HOPKINS as one of the film's actors:
