Sql queries

Example

SELECT *
FROM  users
WHERE username = "dvader"
AND   age > 40;

Query Components

SELECT

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

JOINS

See sql: joins

Techniques

Nested Queries

SELECT user_Id
FROM (
   SELECT DISTINCT     d.user_Id                                           ,
                       CONCAT(u.firstName, " ", u.lastName)  user_fullName
   FROM                userDepartmentTable             d
   INNER JOIN          userTable AS u ON d.user_Id  =  u.user_Id
   WHERE               d.department_Id              =  2
) u
WHERE u.user_fullName REGEXP 'andr%';

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