Kevin Bradwick

SQL JOIN statements

When working with relational databases, you will often find that the data your require can reside on multiple tables. In simple terms, a join statement brings together these tables using a common identifier. Joins are the best way to query a database whilst improving transaction efficiency.

An example

We’ll create two tables, a users and account table. The account table holds account names (e.g. Guest, Admin, Super Admin) and the users table contains one foreign key that relates to the account type.

The structure looks like this;

And here’s some data that we’ll work with…

Now to perform a simple join of these two tables, we could use…

This simple query will return all records but using this as a base we can further customise our query to make it more useful. If we wanted to find out all users who have Admin accounts we’d use this statement.

SELECT * FROM users u LEFT JOIN accounts a ON u.account_type = a.id WHERE a.name = 'Admin'

// or we could use the ID from the accounts table

SELECT * FROM users u LEFT JOIN accounts a ON u.account_type = a.id WHERE a.id = 2

A note on usage

This is MySQL join in its most simplest terms. You can join multiple tables providing you database design allows it. So with that cleared up, if you are not already using joins in your queries, start using them and cut down on the number of transactions you make…it’ll also make your life a lot easier retrieving data.