Sql queries

Example

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

Query Components

SELECT

Determines what columns you want to return.

SELECT *            # select all columns
SELECT col1, col2   # select col1, and col2 only
SELECT col1 AS foo  # rename column in results table

DISTINCT

Distinct only returns one result per unique value of N

SELECT DISTINCT username FROM  users WHERE last_name = "skywalker";

CONCAT

CONCAT lets you combine columns into one

SELECT CONCAT(first_name, " ", last_name) AS fullname FROM users;

SUBSTRING_INDEX

SUBSTRING_INDEX lets you split/tokenize a string

SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2)
##> www.mysql

LIMIT

LIMIT lets you choose only the first N results

SELECT * FROM users LIMIT 10

ORDER BY

SELECT * FROM users ORDER BY first_name ASC;

JOINS

See sql: joins

WHERE

Defines the Matches that will be selected.

Basics

SELECT * FROM users WHERE name = 'Smith' AND age < 30;           # AND
SELECT * FROM users WHERE name = 'Smith' OR age < 30;            # OR
SELECT * FROM users WHERE age BETWEEN  30 AND 40;                # BETWEEN
SELECT * FROM users WHERE name IN ('john', 'jane', 'iggy');      # IN
SELECT * FROM users WHERE name NOT IN ('john', 'jane', 'iggy');  # NOT IN
SELECT * FROM users WHERE first_name LIKE "Andr%";               # LIKE (glob match)
SELECT * FROM users WHERE first_name REGEXP "^A.*"               # REGEXP

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

ORDER BY

SELECT lastName, firstName FROM customers ORDER BY lastName;		# Returned results order determined by lastName
SELECT lastName, firstName FROM customers ORDER BY lastName DESC;	# Returned results order determined by lastName, order inverted

SELECT orderNumber, status														# Custom Sort order based on contained data
FROM orders
ORDER BY FIELD(status, 'In Process',
                       'On Hold',
                       'Cancelled',
                       'Resolved',
                       'Disputed',
                       'Shipped');