Skip to content

Instantly share code, notes, and snippets.

View dan81989's full-sized avatar

dan81989

1 follower · 0 following
View GitHub Profile
@dan81989
dan81989 / full-join.sql
Created May 25, 2016 18:30
For a list of all records from both tables, we can use a full join:
select first_name, last_name, order_date, order_amount
from customers c
full join orders o
on c.customer_id = o.customer_id
@dan81989
dan81989 / right-join-alt.sql
Created May 25, 2016 18:30
Why would such a right join be useful? Simply adding a “where first_name is NULL” line to our SQL query returns a list of all orders for which we failed to record information about the customers who placed them:
select first_name, last_name, order_date, order_amount
from customers c
right join orders o
on c.customer_id = o.customer_id
where first_name is NULL
@dan81989
dan81989 / right-join.sql
Created May 25, 2016 18:27
Right join is a mirror version of the left join and allows to get a list of all orders, appended with customer information, if such information is available:
select first_name, last_name, order_date, order_amount
from customers c
right join orders o
on c.customer_id = o.customer_id
@dan81989
dan81989 / left-join-alt.sql
Created May 25, 2016 18:26
Why would such a left join be useful? Simply adding a “where order_date is NULL” line to our SQL query will return a list of all customers who have not placed an order:
select first_name, last_name, order_date, order_amount
from customers c
left join orders o
on c.customer_id = o.customer_id
where order_date is NULL
@dan81989
dan81989 / left-join.sql
Created May 25, 2016 18:23
If we wanted to simply append information about orders to our customers table, regardless of whether a customer placed an order or not, we would use a left join. A left join returns all records from table A and any matching records in table B.
select first_name, last_name, order_date, order_amount
from customers c
left join orders o
on c.customer_id = o.customer_id
@dan81989
dan81989 / inner-join.sql
Created May 24, 2016 20:53
Let’s say we wanted to get a list of those customers who placed an order and the details of the order they placed. This would be a perfect fit for an inner join, since an inner join returns records at the intersection of the two tables.
select first_name, last_name, order_date, order_amount
from customers c
inner join orders o
on c.customer_id = o.customer_id
@dan81989
dan81989 / all-orders-placed-by-a-customer
Created May 23, 2016 20:40
Let’s say we want to find all orders placed by a particular customer. We can do this by joining the customers and orders tables together using the relationship established by the customer_id key.
select order_date, order_amount
from customers
join orders
on customers.customer_id = orders.customer_id
where customer_id = 3
@dan81989
dan81989 / typicalsql.sql
Created May 20, 2016 14:17
From Troy Thibodeaux's SQL tutorial: https://github.com/tthibo/SQL-Tutorial/blob/master/tutorial_files/part3.textile
CREATE TABLE "contributors" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"last_name" VARCHAR,
"first_name" VARCHAR,
"middle_name" VARCHAR,
"street_1" VARCHAR,
"street_2" VARCHAR,
"city" VARCHAR,
"state" VARCHAR,
"zip" VARCHAR,
@dan81989
dan81989 / typical-sql-query.sql
Created May 20, 2016 14:05
From: https://github.com/tthibo/SQL-Tutorial/blob/master/tutorial_files/part3.textile
CREATE TABLE "contributors" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"last_name" VARCHAR,
"first_name" VARCHAR,
"middle_name" VARCHAR,
"street_1" VARCHAR,
"street_2" VARCHAR,
"city" VARCHAR,
"state" VARCHAR,
"zip" VARCHAR,