What's the point of a database?

Common Solutions:

$key => $value

Common Solutions:

The Old Fashioned

SQL + Relational Database

Relational Data

Relational Data

SQL (es-kyü-el)

A Query's Components

1. What you want

   SELECT the fields, aggregates, or other calculations

2. Pool of data

   FROM, JOIN all the data required (usually far more)

3. Conditions (on data pool)

   WHERE the pool of data satisfies certain conditions

4. Combinators/filters

   GROUPed BY fields to combine rows returned, HAVING certain conditions applied after being grouped

5. Presentation of results

   ORDERed BY field(s) and/or LIMITed to a specific number of records (starting at a position)

Pseudo-SQL

A list of five active users & net balance, whose net balance is less than $200, with the smallest balances first

What you want

User data & his/her net balance

SELECT users.name ,
       SUM(accounts.balance) AS total

Pseudo-SQL

A list of five active users & net balance, whose net balance is less than $200, with the smallest balances first

Pool of data

User & account data

FROM users
LEFT JOIN account ON accounts.user_id = users.id

Pseudo-SQL

A list of five active users & net balance, whose net balance is less than $200, with the smallest balances first

Conditions on data pool

Only active users

WHERE users.active = 1

Pseudo-SQL

A list of five active users & net balance, whose net balance is less than $200, with the smallest balances first

Combinators/filters

One row per user, and only for users with a net balance less than $200

GROUP BY users.id
HAVING total < 200

Pseudo-SQL

A list of five active users & net balance, whose net balance is less than $200, with the smallest balances first

Presentation

Only show the five smallest net balances (in order)

ORDER BY total ASC
LIMIT 5

The SQL

A list of five active users & net balance, whose net balance is less than $200, with the smallest balances first

SELECT users.name ,
       SUM(account.balance) AS total
FROM users
LEFT JOIN account ON accounts.user_id = users.id
WHERE users.active = 1
GROUP BY users.id
HAVING total < 200
ORDER BY total ASC
LIMIT 5

The Details

What about the details?!

• Join type
• Apply conditions to pool of data or after grouping?
• or even to a join?
• What is the main table and what is joined?
• How do I group? By what?
• What if some tables don't have related records?
• What if I want to ignore some records?

It's in the data!

SQL itself is only a handful of building blocks.

How you use them depends entirely on the data you have, and what data you want out.

So let's look at the data...

Relationships

,

DB Schema

One to One

Suppose we want to see every license
and its driver:

SELECT person.first_name , 
       person.last_name , 
       license.number
FROM license
LEFT JOIN person ON license.person_id = person.id

DB Schema

One to One

or none
or many

Suppose we want to see everyone's name
and license number:

SELECT person.first_name , 
       person.last_name , 
       license.number
FROM person
LEFT JOIN license ON license.person_id = person.id

What Happened?

What Happened?

SELECT ...
FROM license
LEFT JOIN person ...

What Happened?

SELECT ...
FROM person
LEFT JOIN license ...

DB Schema (part ii)

Is there anything we can learn from just looking at the schema?

Many-to-Many

(Reminder: A driver may drive many cars,
and each car can have many drivers)

Many-to-Many
=
One-to-many + One-to-many

Many-to-Many

SELECT person.first_name , 
       car.model 
FROM person
LEFT JOIN pivot ON pivot.person_id = person.id
LEFT JOIN car ON pivot.car_id = car.id

Many-to-Many

SELECT person.first_name , 
       car.model 
FROM car
LEFT JOIN pivot ON pivot.car_id = car.id
LEFT JOIN person ON pivot.person_id = person.id

Many-to-Many

SELECT person.first_name , 
       car.model 
FROM pivot
LEFT JOIN person ON pivot.person_id = person.id
LEFT JOIN car ON pivot.car_id = car.id

Common Mistakes: Uniqueness

Common Mistakes: Uniqueness

SELECT 
       person.first_name , 
       car.model 
FROM person
LEFT JOIN pivot ON pivot.person_id = person.id
LEFT JOIN car ON pivot.car_id = car.id

The wrong way (1):

SELECT DISTINCT
       person.first_name , 
       car.model 
FROM person
LEFT JOIN pivot ON pivot.person_id = person.id
LEFT JOIN car ON pivot.car_id = car.id

The wrong way (2):

SELECT person.first_name , 
       car.model 
FROM person
LEFT JOIN pivot ON pivot.person_id = person.id
LEFT JOIN car ON pivot.car_id = car.id
GROUP BY person.id , car.model

So. Good queries?

1. Know your data!
2. Break the query into small pieces
3. Consider what makes data unique
4. Start with the data you want returned per row, and watch row count
5. Remember: Your test data set isn't always complete

Questions?