More Theory Questions
NULL, keys, and DELETE.
Here's another round of theory questions. Nulls, keys, and the three ways to get rid of data. Each one has a short answer you can learn by heart.

- NULLs
- Keys
- Three ways to remove data
Why not = NULL?
Question four. Why do we write is null, instead of equals null? Let's see. Run it. Equals null finds zero customers. Is null finds fifteen. Null means unknown, and comparing anything to unknown gives unknown, never true. So equals null never matches. Always use is null.
SELECT COUNT(*) AS using_equals
FROM customers
WHERE city = NULL;
SELECT COUNT(*) AS using_is_null
FROM customers
WHERE city IS NULL;โโโโโโโโโโโโโโโโ โ using_equals โ โโโโโโโโโโโโโโโโค โ 0 โ โโโโโโโโโโโโโโโโ โโโโโโโโโโโโโโโโโ โ using_is_null โ โโโโโโโโโโโโโโโโโค โ 15 โ โโโโโโโโโโโโโโโโโ
Primary vs foreign key
Question five. Primary key versus foreign key. A primary key uniquely identifies each row in its own table, and can't be null. A foreign key is a column that points to the primary key of another table. That's how tables link together.
PRIMARY KEY
- Identifies each row
- Unique, never NULL
- customers.customer_id
FOREIGN KEY
- Points to another table
- Can repeat
- orders.customer_id
DELETE, TRUNCATE, DROP
Question six. Delete, truncate, and drop. Delete removes the rows you choose with where, and can be rolled back. Truncate removes every row, very fast, but keeps the table. Drop removes the whole table, structure and all. SQLite doesn't have truncate. A delete without a where does the same job.
| Command | Removes | Keeps the table? |
|---|---|---|
| DELETE ... WHERE | chosen rows | Yes |
| TRUNCATE | all rows, fast | Yes |
| DROP TABLE | everything | No |
Summary
Is null, not equals null. Primary keys identify, foreign keys point. Delete, truncate, drop. Last round of theory next.
Key takeaways- IS NULL, never = NULL
- PK identifies, FK points
- DELETE / TRUNCATE / DROP