dan81989’s gists

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 / 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 / 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 / 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 / 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 / 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 / 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 / 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 / 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,