When to use the IN operator in your SQL WHERE statement

Finding your SQL statements growing and difficult to understand?

Inherited some code that contains complex looking SQL statements?

Let me show you one way to make them easier to read and understand…

Sooner or later you will have to create a SQL statement with multiple WHERE conditions, usually AND and OR’s.

If you have three or more conditions your SQL can easily become difficult to read and difficult to understand the logic.

Your brain (and mine) can start to hurt as you wonder…

Are all those WHERE conditions returning the expected results?

Database table cars_for_sale

id for_sale dealer make model engine price description
1 1 Crumps cars Audi S4 V8 $9999 Lorem Ipsum…
2 1 Crumps cars Audi A4 V6 $9999 Lorem Ipsum…
3 1 Crumps cars Audi Q7 V10 $9999 Lorem Ipsum…
4 0 Crumps cars Audi A4 4cyl $9999 Lorem Ipsum…
5 1 Crumps cars Audi A4 4cyl $9999 Lorem Ipsum…
6 1 Another dealer Audi S4 V8 $9999 Lorem Ipsum…
7 1 Bobs cars Audi A4 V6 $9999 Lorem Ipsum…
8 0 Crumps cars Audi Q7 V10 $9999 Lorem Ipsum…
9 0 Crumps cars Audi A6 V8 $9999 Lorem Ipsum…
10 1 Crumps cars Audi A8 V8 $9999 Lorem Ipsum…

Consider the following SQL query a reader recently sent to me.

(Not the actual query, but modified here by me for privacy for this example)

SELECT id, make, model, price, description
FROM cars_for_sales
WHERE for_sale = 1
AND dealer = 'Crumps cars'
AND (engine != 'V12' OR engine != 'V10' OR engine != 'V8' OR engine != 'V6')
ORDER BY make, model

The reader wants all cars for sale at ‘Crumps cars’ dealership but don’t have a certain engine type, V12, V10, V8, V6.

Immediately I find the multiple conditions difficult to read and understand if the conditions are going to work together.

If there were more OR’s than above it’s only going to get worse…

What if you could save the brain ache and make your WHERE statement easier to write and read!

Well there is a simple way in my my mind and that’s using the IN comparison operator in your WHERE clause.

Let’s use it to rescue this SQL statement and rewrite it like this:

SELECT id, make, model, price, description
FROM cars_for_sales
WHERE for_sale = 1
AND dealer = 'Crumps cars'
AND engine NOT IN ('V12','V10','V8','V6')
ORDER BY make, model

Now you can simply have a comma seperated list of multiple values.
Each of which we don’t want the engine column to match with, by using the NOT IN operator.

Alternatively if you do want them to match just remove the NOT

Then every result will have a “V” engine from the list

SELECT id, make, model, price, description
FROM cars_for_sales
WHERE for_sale = 1
AND dealer = 'Crumps cars'
AND engine IN ('V12','V10','V8','V6')
ORDER BY make, model

How to use PDO to UPDATE data in MySQL using PHP

You need to get data safely updated from a web page to a MySQL table in your database

You need to sanitize that data from user input and protect against SQL injections.

The following simple example will show you how to accomplish that.

These are the ingredients you will need:

  1. A MySQL database
  2. A table to store the data in the above database
  3. The credentials to above (the host location, username and password)
  4. Which you can use to create a connection in your PHP page
  5. A form with fields to input the data
  6. A way to sanitize the data
  7. Safely update the data

What if you don’t have all of the above set-up?

Don’t worry at the end will be a link to example PHP source code along with the SQL statements to create the database and table.

PHP PDO (PHP Data Objects) makes it very easy to UPDATE data from your table.

First, to connect to the database with PDO is very simple:

$dbh = new PDO('mysql:host=localhost;dbname='. $db_name, $db_username, $db_password);

The above line creates a connection and stores it in the $dbh variable as an object

That variable can then be used to save the data using an UPDATE statement:

$stmt = $dbh->prepare('UPDATE makes SET make = :make WHERE id = :id');

This prepares a statement which we can then bind the make parameters safely too, protecting against SQL injections.

We use PHP’s filer_input() function to santize the data coming from the input fields POSTed to the server from our form:

$id = filter_input(INPUT_POST, 'id', FILTER_SANITIZE_NUMBER_INT);
and
$make = filter_input(INPUT_POST, 'make', FILTER_SANITIZE_STRING);
Then using the bindParam() function we can bind the $id variable into the statement:
$stmt->bindParam(':id', $id, PDO::PARAM_INT);

Then the $make variable can also be bound to the prepared statement:

$stmt->bindParam(':make', $make, PDO::PARAM_STR);

Then the statement is executed to run the prepared SQL statement, resulting in the record being updated in the table:

$stmt->execute()

These are the key lines to connect to the database. Sanitize the data and UPDATE the data in the database.

Of course the above code lines don’t show the HTML need to make the form and input fields.

You can find that here on github as a simple PHP PDO CRUD Demo. Please download / fork the code and try it out.

The demo is a simple CRUD example of using PHP, PDO and MySQL to display a list of car makes

This post is a part of a series, you can read more about viewing the data with PDO here

How to use PDO to DELETE data from MySQL using PHP

You need to delete data safely from a web page and your MySQL table in your database

You need to sanitize that data from user input and protect against SQL injections.

The following simple example will show you how to accomplish that.

These are the ingredients you will need:

  1. A MySQL database
  2. A table to store the data in the above database
  3. The credentials to above (the host location, username and password)
  4. Which you can use to create a connection in your PHP page
  5. A list of records displaying your data
  6. A way to select and delete a record
  7. Safely delete the data

What if you don’t have all of the above set-up?

Don’t worry at the end will be a link to example PHP source code along with the SQL statements to create the database and table.

PHP PDO (PHP Data Objects) makes it very easy to DELETE data from your table.

First, to connect to the database with PDO is very simple:

$dbh = new PDO('mysql:host=localhost;dbname='. $db_name, $db_username, $db_password);

The above line creates a connection and stores it in the $dbh variable as an object

That variable can then be used to delete the data using a DELETE statement:

$stmt = $dbh->prepare('DELETE FROM makes WHERE id = :id');

This prepares a statement which we can then bind the id parameter safely too, protecting against SQL injections.

We use PHP’s filer_input() function to santize the data and make sure it’s an integer. The GET parameter comes from the query string in the URL of the delete.php page:

$id = filter_input(INPUT_GET, 'id', FILTER_SANITIZE_NUMBER_INT);

The $id variable can then be bound to the prepared statement:

$stmt->bindParam(':id', $id, PDO::PARAM_INT);

Then the statement is executed to run the prepared SQL statement, resulting in the record being deleted from the MySQL database table:

$stmt->execute()

These are the key lines to connect to the database. Sanitize the query string variable and DELETE the record in the database.

Of course the above doesn’t show the HTML need to make the form and input field.

You can find that here, on github as a simple PHP PDO CRUD Demo. Please download / fork the code and try it out.

The demo is a simple CRUD example of using PHP, PDO and MySQL to display a list of car makes

This post is a part of a series, you can read more about viewing the data with PDO here. Which generates the delete link to delete.php

How to use PDO to INSERT data into MySQL using PHP

You need to get data safely from a web page to a MySQL table in your database

You need to sanitize that data from user input and protect against SQL injections.

The following simple example will show you how to accomplish that.

These are the ingredients you will need:

  1. A MySQL database
  2. A table to store the data in the above database
  3. The credentials to above (the host location, username and password)
  4. Which you can use to create a connection in your PHP page
  5. A form with fields to input the data
  6. A way to sanitize the data
  7. Safely insert the data

What if you don’t have all of the above set-up?

Don’t worry at the end will be a link to example PHP source code along with the SQL statements to create the database and table.

PHP PDO (PHP Data Objects) makes it very easy to INSERT data from your table.

First, to connect to the database with PDO is very simple:

$dbh = new PDO('mysql:host=localhost;dbname='. $db_name, $db_username, $db_password);

The above line creates a connection and stores it in the $dbh variable as an object

That variable can then be used to save the data using an INSERT statement:

$stmt = $dbh->prepare("INSERT INTO makes (make) VALUES (:make)");

This prepares a statement which we can then bind the make parameter safely too, protecting against SQL injections.

We use PHP’s filer_input() function to santize the data coming from the input field POSTed to the server from our form:

$make = filter_input(INPUT_POST, 'make', FILTER_SANITIZE_STRING);

The $make variable can then be bound to the prepared statement:

$stmt->bindParam(':make', $make, PDO::PARAM_STR);

Then the statement is executed to run the prepared SQL statement, resulting in the record being added to the table:

$stmt->execute()

These are the key lines to connect to the database. Sanitize the data and INSERT in the database.

Of course the above doesn’t show the HTML need to make the form and input field.

You can find that here though on github as a simple PHP PDO CRUD Demo. Please download / fork the code and try it out.

The demo is a simple CRUD example of using PHP, PDO and MySQL to display a list of car makes

This post is a part of a series, you can read more about viewing the data with PDO here

How to use PDO to SELECT data from MySQL using PHP example

You have a MySQL table of data in your database, you want to display it on a webpage, so it can be displayed and viewed by other people.

You need to have a few things in order to accomplish this:

  1. A MySQL database
  2. A table containing data in the above database
  3. The credentials to above (the host location, username and password)
  4. Which you can use to create a connection in your PHP page
  5. A way to iterate through the records so they can be echoed out
  6. The table field names so we can display them on the page

What if you don’t have all of the above set-up?

Don’t worry at the end will be a link to some example PHP source code along with the SQL statements to create the database and table.

PHP PDO (PHP Data Objects) makes it very easy to SELECT data from your table.

To connect to the database with PDO is very simple:

$dbh = new PDO('mysql:host=localhost;dbname='. $db_name, $db_username, $db_password);

The above line creates a connection and stores it in the $dbh variable as an object

That variable can then be used to retrieve the records using a SELECT statement:

foreach($dbh->query('SELECT * FROM makes ORDER BY make ASC') as $row) {

This allows us to use the resulting rows of data returned by the query.

Using the field names from the database table, we can output the data with an echo statement:

echo "<td>". $row['make'] ."</td>";

After we’ve finished, set the variable to null to free up the connection:

$dbh = null;

So with the above few lines of code, we can connect to the database. Retrieve the data and output it.

To make everything look good, we need a few more lines of code to create a table.

The full source code is available here on github as a simple PHP PDO Crud Demo.

The demo is a simple CRUD example of using PHP, PDO and MySQL to display a list of car makes

How to INNER JOIN on the same table twice in SQL statement

Having more than one INNER JOIN to the same table can be confusing.

More than one INNER JOIN can create a messy SQL statement.

If you don’t use aliases they can become difficult to read.

So join them with aliases, as explained below.

For example say you have the following two tables: Users and Products. When a Product is created, the Users id that created it is stored. Then when it’s modified the Users id that modified the record is stored.

If you want to find out who created the product, you can do that with a simple SQL query like this:

SELECT
p.id
, u.firstname AS 'creator_firstname'
, u.lastname AS 'creator_lastname'
FROM products p
INNER JOIN users u
ON p.created_by_user_id = u.id

Table users is aliased as u, products as p.

Then you can prepend the alias to SELECT a field from a table.

p.id is equivalent of writing products.id

If you want to find out who modified the product you can do that with a similar query:

SELECT
p.id
, u.firstname AS 'modifier_firstname'
, u.lastname AS 'modifier_lastname'
FROM products p
INNER JOIN users u
ON p.modified_by_user_id = u.id

But how can you combine both queries to know the user that created and modified the products in one query?

Just join the Users table twice, but you need to use a different alias each time you use the same table:

SELECT
p.id
, u1.firstname AS 'creator_firstname'
, u1.lastname AS 'creator_lastname'
, u2.firstname AS 'modifier_firstname'
, u2.lastname AS 'modifier_lastname'
FROM products p
INNER JOIN users u1
ON p.created_by_user_id = u1.id
INNER JOIN users u2
ON p.modified_by_user_id = u2.id

But what if a product has only been created and hasn’t yet been modified? In which case the modified_by_user_id should be null in your products table.  Then the above query won’t be able to join twice to the users table. Unless it has a user record with a null id, which it shouldn’t.  In this case you need to use LEFT JOIN like this:

SELECT
p.id
, u1.firstname AS 'creator_firstname'
, u1.lastname AS 'creator_lastname'
, u2.firstname AS 'modifier_firstname'
, u2.lastname AS 'modifier_lastname'
FROM products p
INNER JOIN users u1
ON p.created_by_user_id = u1.id
LEFT JOIN users u2
ON p.modified_by_user_id = u2.id

So now you can join the same table twice in single efficient query.

Here are all the SQL statements from above and the CREATE table statements:


For more about MySQL please check out the MySQL terminology guide

Interested in more SQL tips? Please subscribe below and you’ll be sure to receive them!

Or follow me on Twitter

What alternatives database engines are there to MySQL?

Unsure what alternatives there are to MySQL?

Looking for increases in MySQL performance?

You might not realize that there are some compatible relational database engines. That you can drop in to replace MySQL.

You can even run SQLite without needing a server stack.

SQLite is the most widely deployed database engine in the world

Each engine has it’s own pros and cons to consider before switching from one to another. Most are available as a drop in replacement for MySQL, but yet dropping out and back to MySQL may not be as smooth. Development of new versions means the code bases and features diverge from each other.

MariaDB, Percona Server and Amazon Aurora all offer performance improvements over vanilla MySQL. All for free:

Relational Database Engine Details Costs Link
Amazon Aurora Amazon Aurora is a MySQL-compatible relational database engine that combines the speed and availability of high-end commercial databases with the simplicity and cost-effectiveness of open source databases. Amazon Aurora provides up to five times better performance than MySQL with the security, availability, and reliability of a commercial database at one tenth the cost. AWS has a free usage tier, otherwise costs depend on server spec x usage. Amazon Aurora
MariaDB One of the most popular database servers. Made by the original developers of MySQL. Guaranteed to stay open source. Free https://mariadb.org/
MariaDB subreddit
MySQL MySQL is the world’s most popular open source database. Whether you are a fast growing web property, technology ISV or large enterprise, MySQL can cost-effectively help you deliver high performance, scalable database applications. Community Edition is free. MySQL
MySQL subreddit
Percona Server Is a free, fully compatible, enhanced, open source drop-in replacement for MySQL that provides superior performance, scalability and instrumentation. Percona Server’s self-tuning algorithms and support for extremely high-performance hardware delivers excellent performance and reliability. Free Percona Server
Percona Server subreddit
SQLite SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed database engine in the world. Free SQLite
SQLite subreddit

How to join a table many times in a single SQL query statement?

Having more than one join to the same table within a query can be confusing.

More than one join can create a messy SQL statement.

If you don’t use aliases they can be difficult to read.

If your database tables need to have many columns with the same relationship to another table. You can join them with aliases in a single efficient query.

Need a working example, with sample SQL statements? Check out my other post: How to join the same table twice in a single SQL query statement?

For more about MySQL please check out the MySQL terminology guide

Interested in more SQL tips? Please subscribe in the box below, for a weekly update of my newest posts every Tuesday!

Reasons to switch from MySQL to MariaDB

Thinking of moving from regular MySQL to MariaDB?

MariaDB has a considerable performance gain over MySQL 5.6

Need any more reasons than performance to switch?

  1. Switching from MySQL to MariaDB is easy, it’s still considered a drop in replacement.
  2. MariaDB definitely has more of an open source attitude.
  3. Galera implementation is better.
  4. Maria comes by default with some distros, like the Red Hat series.

I recently found MariaDB 10 was available on my hosting environment. I saw that MariaDB was compatible with MySQL having not heard of it before then. I tried setting it up and switching was dead easy, just like MySQL and I didn’t have to make any code changes.

If you found the reasons above to consider switching to MariaDB helpful. Please comment and subscribe below so you don’t miss out on my next tips…

How to join the same table three times in a single SQL query statement?

Having more than one join to the same table can be confusing.

More than one join can quickly create a messy SQL statement. If you don’t use aliases they can quickly become difficult to read.

If your database tables need to have three or more columns with the same relationship to another table. You can easily join them with the help of aliases, as explained below.

For example say you have the following two tables: Users and Products. When a Product is created, the Users id that created it is stored. Then when it’s modified the Users id that modified the record is stored. Finally a Users id is recorded to approve the changes.

Products table fields: Comments
id Primary Key
created_by_user_id Foreign Key to Users table
modified_by_user_id Foreign Key to Users table
approved_by_user_id Foreign Key to Users table
Users table fields: Comments
id Primary Key
first_name
last_name

If you want to find out who created the product, you can do that with a simple SQL query like this:

SELECT
p.id
, u.firstname AS 'creator_firstname'
, u.lastname AS 'creator_lastname'
FROM products p
INNER JOIN users u
ON p.created_by_user_id = u.id

If you want to find out who modified the product you can do that with a similar query:

SELECT
p.id
, u.firstname AS 'modifier_firstname'
, u.lastname AS 'modifier_lastname'
FROM products p
INNER JOIN users u
ON p.modified_by_user_id = u.id

If you want to find out who approved the product you can do that with a similar query:

SELECT
p.id
, u.firstname AS 'approver_firstname'
, u.lastname AS 'approver_lastname'
FROM products p
INNER JOIN users u
ON p.approved_by_user_id = u.id

But how can you combine all three queries to know the usera that created, modified and approved the products in one query?

Just join the Users table three times, but you need to use a different alias each time you JOIN the same table:

SELECT
p.id
, u1.firstname AS 'creator_firstname'
, u1.lastname AS 'creator_lastname'
, u2.firstname AS 'modifier_firstname'
, u2.lastname AS 'modifier_lastname'
, u3.firstname AS 'approver_firstname'
, u3.lastname AS 'approver_lastname'
FROM products p
INNER JOIN users u1
ON p.created_by_user_id = u1.id
INNER JOIN users u2
ON p.modified_by_user_id = u2.id
INNER JOIN users u3
ON p.approved_by_user_id = u3.id

But what if a product has only been created and modified but hasn’t yet been approved? In which case the approved_by_user_id should be null in your products table.  Then the above query won’t be able to join twice to the users table. Unless it has a user record with a null id, which it shouldn’t.  In this case you need to use a LEFT JOIN like this:

SELECT
p.id
, u1.firstname AS 'creator_firstname'
, u1.lastname AS 'creator_lastname'
, u2.firstname AS 'modifier_firstname'
, u2.lastname AS 'modifier_lastname'
, u3.firstname AS 'approver_firstname'
, u3.lastname AS 'approver_lastname'
FROM products p
INNER JOIN users u1
ON p.created_by_user_id = u1.id
INNER JOIN users u2
ON p.modified_by_user_id = u2.id
LEFT JOIN users u3
ON p.approved_by_user_id = u3.id

So now you can join the same table three times with aliases in single efficient query.

Here are all the SQL statements from above and the CREATE table statements:


For more about MySQL please check out the MySQL terminology guide

Interested in more SQL tips? Please pop your email in the box below and you’ll be sure to receive them!