Sql queries

Example

SELECT *                   # select all rows
FROM  users                # from users table
WHERE username = "dvader"  # where the 'username' column value is 'dvader'
AND   age > 40;            # and the 'age' column value is above 40

Query Components

SELECT which columns you'd like to see, and how you'd like them to be presented (order, max results, etc).

SELECT *                               # select all columns
SELECT name, age FROM users;           # select name, and age only
SELECT name AS userame FROM users;     # rename column in results table
SELECT DISTINCT name FROM users;       # select 1x row for each unique name in users
SELECT * FROM users LIMIT 10;          # only select first 10 results
SELECT * FROM users ORDER BY name ASC  # sort results in ascending order by name

After your selection you may:

• use sql joins to associate the queried table's data to other tables.
  (ex: find users assigned to a project)
  
  
• use sql aggregate functions to operate on groups of rows
  (ex: find average age of users in users table)
  
  
• use sql comparison operators in your WHERE statement to determine what you want to select.
  (ex: find users whose name starts with an 'A')

Techniques

Nested Queries

You can nest SQL queries and treat them as tables.

The example below finds users.id values
that are not present in the many:many user_departments table,
by joining a user table query to itself.

SELECT users.*
FROM users

LEFT JOIN (
  SELECT DISTINCT users.id AS user_id
  FROM   user_departments
  INNER JOIN users ON users.id = user_departments.user_id
) AS users_with_departments
ON users_with_departments.user_id = users.id

WHERE users_with_departments.user_id IS NULL;

You can also nest a query within a WHERE condition.
Joined tables are accessible within this nested query.

SELECT *
FROM users
LEFT JOINS assignments ON assignments.user_id = users.id
WHERE ((SELECT COUNT(1) FROM users WHERE assignments.active = 1) > 0)  # the outer 'assignmens' is in scope here

Dynamically Defined Tables

Some databases do not implement the SQL IN operator.
Instead, you can use VALUES.

SELECT *
FROM (
  VALUES (1), (2)
) foo(id);

Most useful within a join

SELECT *
FROM (
  VALUES (1), (2)
) foo(id)

INNER JOIN (
  (VALUES (1)) bar(id)
) ON foo.id = bar.id