How to use SELECT, FROM, WHERE, and AND in SQL statements

The SELECT statement is the most simple of all SQL queries and allows you to retrieve the precise data you want from one or more tables, or even databases. In this simple tutorial I’ll explain how you can use SELECT with the FROM, WHERE and AND clauses to fetch a wide range of specific column values from the database tables in a MySQL database.

Fire up your database server

Before you can start, you’ll need to have access to a MySQL database that you can query. To make this easier to follow along, I’ve created a MySQL database dump file of the popular Online Retail Dataset that you can import into a Docker container.

To get started, you’ll first need to create a MySQL Docker container and then import the SQL database dump to create a new database. Although it sounds scary, the whole process should only take a few minutes and I’ve provided the code you need for every step.

Install and load the packages

To query the MySQL database on your Docker container we’ll need to install a couple of Python packages. To query a database with Pandas you’ll need an SQL driver - a bit of extra code that connects to the database and passes your SQL queries. We’ll be using sqlalchemy and pymysql, so import the packages into your Jupyter environment using the commands below.

!pip3 install sqlalchemy
!pip3 install pymysql

import pandas as pd
from sqlalchemy import create_engine

Create a database connection

Next we need to configure our connection to the MySQL Docker container. This is covered in the tutorial on creating the database itself, but basically involves connecting to the IP address of the server, usually on port 3306, and then defining the ecommerce database to which you want to connect, along with your username and password.

engine = create_engine('mysql+pymysql://root:**********@172.17.0.3:3306/ecommerce')

SELECT * FROM a SQL table

Now you hopefully have a working database connection, we’ll write the most simple of all SQL queries - the SELECT statement. The statement SELECT * FROM customers uses the asterisk wildcard to to MySQL to return all columns in the customers table.

To execute the SQL statement or query, we’ll pass it to the Pandas read_sql_query() function along with the engine connection we created above. Pandas will use SQLAlchemy and PyMSQL to connect to the database, run the query, and return the results in a Pandas dataframe that we’re assigning to the variable df.

Since that’s just a regular Pandas dataframe, we can manipulate it in the same way we would an other dataframe, for example, by using head() to print the first five rows. The ecommerce database contains four database tables called customers, orders, order_items, and products. You can select the data from these tables simply by changing the database table parameter after FROM.

df = pd.read_sql_query("SELECT * FROM customers", con=engine)
df.head()

df = pd.read_sql_query("SELECT * FROM orders", con=engine)
df.head()

df = pd.read_sql_query("SELECT * FROM order_items", con=engine)
df.head()

df = pd.read_sql_query("SELECT * FROM products", con=engine)
df.head()

SELECT specific columns FROM an SQL table

The asterisk operator returns all columns from a database table, but you’ll often want to select only specific columns be returned by your query. To do this, you just pass a comma separated value list of column names instead of *. The order of the columns dictates the order in which they’ll appear in the SQL result set or dataframe.

df = pd.read_sql_query("SELECT customer_id, revenue, orders FROM customers", con=engine)
df.head()

Using WHERE in an SQL statement

The very simple SQL statement we wrote above doesn’t include any clauses to tell MySQL to filter the results. To select only specific rows from the database you need to use the WHERE clause. This takes a column name, i.e. invoice, and a column value to find, i.e. 536365. Running the statement SELECT * FROM order_items WHERE invoice = 536365 will, therefore, return all the order_items columns from invoice 536365.

df = pd.read_sql_query("SELECT * FROM order_items WHERE invoice = 536365", con=engine)
df.head()

Using multiple operators in a WHERE clause using AND

If you want to create a more complex database query, you’ll often need to combine multiple clauses using AND. The below query will select all columns from the order_items table WHERE the country is United Kingdom and the order_date is greater than 2010-10-01 and the qty of units was greater than 10.

query = """
SELECT * 
FROM order_items 
WHERE country = 'United Kingdom'
AND order_date > '2010-10-01'
AND qty > 10
"""

df = pd.read_sql_query(query, con=engine)
df.head()

Using LIKE to return values containing a string

Sometimes, you might want to perform a fuzzy match and return only items that match a partial string. For example, we might want to look at all the products in the order_items table that contain the word white in their product name held in the description column. To do this we can use LIKE along with the % symbol. By putting a percentage symbol either side of the value we’ll return any products that contain the word white.

query = """
SELECT * 
FROM order_items 
WHERE description LIKE '%white%'
"""

df = pd.read_sql_query(query, con=engine)
df.head()

Using LIKE to return values starting with a string

Wrapping two percentage symbols around a string will return all values that contain the string. However, sometimes you might only want to return values that start with a string. To do this, you simply place the percentage symbol at the end.

query = """
SELECT * 
FROM order_items 
WHERE description LIKE 'white%'
"""

df = pd.read_sql_query(query, con=engine)
df.head()

Using LIKE to return values ending with a string

Similarly, to select values that end with a value, you can use LIKE and place the % symbol at the start of the string. This will return only those items that end with the provided value.

query = """
SELECT * 
FROM order_items 
WHERE description LIKE '%white'
"""

df = pd.read_sql_query(query, con=engine)
df.head()

Using NOT LIKE to return values that don’t match a string

To obtain items that do not contain a given value you can use NOT LIKE. Here’s a simple example that returns all products that don’t contain the word white in their product name. You can, of course, use the above approach and return items that start with a string, or end with a string, simply by using the percentage symbol at either end, rather than both ends.

query = """
SELECT * 
FROM order_items 
WHERE description NOT LIKE '%white%'
"""

df = pd.read_sql_query(query, con=engine)
df.head()

Matt Clarke, Tuesday, April 12, 2022