Sql queries

TODO:

this should be broken down into comparison-operators vs select-syntax

Simple Queries

SELECT name, lastname FROM my_table            # only name,lastname columns
SELECT * FROM my_table  WHERE name = 'Smith';  # 

# DISTINCT
SELECT DISTINCT        *       FROM my_table WHERE name='Smith';  # No Duplicate Columns
SELECT DISTINCT name, lastname FROM my_table WHERE name='Smith';  # No Columns returned where name, and lastname
                                                                  # are not a unique combo (can have multiple
                                                                  # johns, and multiple does, but only one john doe)

# OPERATORS
SELECT * FROM my_table  WHERE name = 'Smith'  AND age < 30;          # AND operator
SELECT * FROM my_table  WHERE name = 'Smith'  OR age < 30;           # OR operator
SELECT * FROM my_table  WHERE age BETWEEN  30 AND 40;                # BETWEEN operator
SELECT * FROM my_table  WHERE name IN ('john', 'jane', 'iggy');      # IN operator
SELECT * FROM my_table  WHERE name NOT IN ('john', 'jane', 'iggy');  # NOT IN operator

# RESTRICT RESULTS
SELECT * 
FROM my_table 
LIMIT 0, 200  # Refine results to 200 rows after row 0

# COMBINE FIELDS
SELECT CONCAT(u.firstName, " ", u.lastName) user_fullName  # 'firstName lastName'
FROM my_table

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 'ghis';

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');

SUBSTRING_INDEX

split/tokenize a string.

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