AI Hints
Homeโ€บSQL Tutorialโ€บInterview Questions, Part 3
SQL ยท Lesson 102 of 126

More Theory Questions

NULL, keys, and DELETE.

โฑ 2 min readโ–ถ Video at 2:23:46๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

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.

More Theory Questions
From the video: More Theory Questions
  • 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.

query.sqlsql
SELECT COUNT(*) AS using_equals
FROM customers
WHERE city = NULL;

SELECT COUNT(*) AS using_is_null
FROM customers
WHERE city IS NULL;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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.

CommandRemovesKeeps the table?
DELETE ... WHEREchosen rowsYes
TRUNCATEall rows, fastYes
DROP TABLEeverythingNo

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
Next up: Quick-Fire Quiz โ€” test yourself