SQL - Advanced

Formatting:

In the following examples, specific parts of the queries are marked to make it more understandable:

table name field name value

Security information

When you use user input (for example from a search box) inside a statement, make sure to sanitize (filter special chars like " or ') the input. Depending on your programming language, you can use Prepared Statements for more security. 

Don't trust ANY user input!

Advanced - read the basics before

This article is an addition to the first part SQL - The basics. If you're not familiar with SQL, consider reading this before you jump into the more advanced stuff :) 

Joins / Multiple tables

If you're building a more complex system with multiple tables and relations, you'll most likely face statements with JOIN. For this example, let's pretend we've two tables: users

and posts:

Now imagine a list with all posts and the names of the authors. Now, you can get all posts and create an extra query for each post to get the user by the "user" field. Or, you use one single query to get all the information you need. 

SELECT title FROM posts

This query returns simply the posts table. The first thing we have to change, is to add the table names before the field names, so SQL knows what field is from what table:

SELECT posts.title FROM posts

But, that's not all we want. We need also the name of the user:

SELECT posts.title, users.name FROM posts

Perfect. But SQL doesn't know which row in the "users" table relates to each post. So, we first join the table "users" and add an expression.

SELECT posts.title, users.name FROM posts JOIN users ON users.id = posts.user

With ON we tell SQL, that we want to get the row of users, where the "id" field in the "users" table is equal to the "user" field in the "posts" table.

The result would be:

Note: If you use the same field names in both tables (for example "title" in both tables), the column name in your result is "tablename.fieldname" instead of just the fieldname.