Nearly every backend and data job posting lists the same second skill after Python: SQL. It is how you ask a database a plain question, “show me every customer in Pune,” and get an exact answer in milliseconds. This post covers the SQL basics that do most of the daily work: SELECT, WHERE, ORDER BY, the NULL trap, and the two joins you will actually use.
“Data is a precious thing and will last longer than the systems themselves.”
Tim Berners-Lee
Last Updated: July 2026 | Tested on: Python 3.14.6 (SQLite 3.50) | Difficulty: Beginner | Reading Time: 18 minutes
Here is the mental shift that makes the SQL basics click. A Python list or a JSON file is like a pile of index cards you flip through one by one. A database table is like a spreadsheet with a very fast librarian standing next to it. Instead of writing a loop to filter, sort, and count by hand, you hand the librarian a short sentence and they run to the shelves and bring back exactly the rows you asked for. SQL is that sentence. The language is the same whether the librarian works for SQLite, PostgreSQL, or MySQL, so the queries you learn here move with you for the rest of your career.
We use SQLite for every example because it ships inside Python and needs zero setup. There is no server to install and no account to create. The moment your app needs many machines writing at once, you move the same SQL over to PostgreSQL or MySQL, and almost everything in this post works unchanged, because the core of SQL is an ANSI standard that has barely moved in decades. At the time of writing, SQLite bundled with Python 3.14.6 is engine 3.50, so everything below runs out of the box.
Table of Contents
Set Up a Tiny Shop Database
Every one of the SQL basics in this post runs against the same little shop. We have three tables: customers (who buys), products (what we sell, all vegetarian), and orders (who bought what). This is the classic shape of almost every real database you will ever meet, one table per kind of thing, connected by id columns. Run the script below once and it builds the whole thing from scratch.
Notice one deliberate detail in the data: a customer named Anvi has signed up but has not placed a single order yet, and she also has no city on file. Those two gaps are not mistakes, they are exactly what the joins and the NULL section later will teach you to handle.
📄 seed.py: build a three-table shop database
import sqlite3
conn = sqlite3.connect("shop.db")
cur = conn.cursor()
# Start clean so the script is safe to re-run
cur.executescript("""
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
city TEXT
);
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
category TEXT,
price REAL NOT NULL
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id),
product_id INTEGER REFERENCES products(id),
quantity INTEGER NOT NULL,
order_date TEXT
);
""")
customers = [
(1, "Aditi", "Pune"),
(2, "Anvay", "Mumbai"),
(3, "Aviraj", "Pune"),
(4, "Niranjan", "Nagpur"),
(5, "Anvi", None), # a customer with no city on file
]
products = [
(1, "Paneer Tikka Wrap", "food", 180.0),
(2, "Masala Chai", "drink", 40.0),
(3, "Veg Biryani", "food", 220.0),
(4, "Filter Coffee", "drink", 50.0),
(5, "Gulab Jamun", "dessert", 120.0),
]
orders = [
(1, 1, 1, 2, "2026-06-01"),
(2, 1, 2, 1, "2026-06-01"),
(3, 2, 3, 1, "2026-06-02"),
(4, 3, 1, 1, "2026-06-03"),
(5, 3, 5, 3, "2026-06-03"),
(6, 4, 4, 2, "2026-06-04"),
(7, 1, 3, 1, "2026-06-05"),
# note: customer 5 (Anvi) has placed no orders yet
]
cur.executemany("INSERT INTO customers VALUES (?, ?, ?)", customers)
cur.executemany("INSERT INTO products VALUES (?, ?, ?, ?)", products)
cur.executemany("INSERT INTO orders VALUES (?, ?, ?, ?, ?)", orders)
conn.commit()
print("Seeded:",
cur.execute("SELECT COUNT(*) FROM customers").fetchone()[0], "customers,",
cur.execute("SELECT COUNT(*) FROM products").fetchone()[0], "products,",
cur.execute("SELECT COUNT(*) FROM orders").fetchone()[0], "orders")
conn.close()
▶ Output
Seeded: 5 customers, 5 products, 7 orders
What happened here: The CREATE TABLE statements define the shape of each table, its column names and types. PRIMARY KEY marks the id that uniquely identifies a row, and REFERENCES customers(id) on the orders table is a foreign key, a written promise that every customer_id in orders points at a real customer. executemany inserts a whole list of rows in one call. After conn.commit() writes it to disk, we have a real relational database sitting in a single file called shop.db. Everything from here on is just asking it questions.
SELECT, WHERE, ORDER BY, LIMIT, DISTINCT
SELECT is the verb you will type more than any other. It means “give me these columns.” You bolt on clauses to narrow and shape the result: WHERE filters rows, ORDER BY sorts them, LIMIT caps how many come back, and DISTINCT removes duplicates. Think of ordering at a food stall: SELECT is what you want, WHERE is your condition (“only the ones under 200 rupees”), ORDER BY is “cheapest first,” and LIMIT 2 is “just the top two, please.”
To keep the output readable, we use one tiny helper called show() that runs a query and prints the rows as a plain text table. You will reuse it in every section below, so read it once here.
📄 select_demo.py: the four clauses you will use constantly
import sqlite3
conn = sqlite3.connect("shop.db")
conn.row_factory = sqlite3.Row # lets us read columns by name
def show(sql, params=()):
"""Run a query and print the rows as a plain text table."""
rows = conn.execute(sql, params).fetchall()
if not rows:
print("(no rows)")
return
cols = rows[0].keys()
width = {c: max(len(c), max(len(str(r[c])) for r in rows)) for c in cols}
print(" | ".join(c.ljust(width[c]) for c in cols))
print("-+-".join("-" * width[c] for c in cols))
for r in rows:
print(" | ".join(str(r[c]).ljust(width[c]) for c in cols))
print()
print("== All products ==")
show("SELECT id, name, price FROM products")
print("== Food only, cheapest first ==")
show("SELECT name, price FROM products WHERE category = ? ORDER BY price ASC", ("food",))
print("== Two most expensive items ==")
show("SELECT name, price FROM products ORDER BY price DESC LIMIT 2")
print("== Which cities do customers come from? ==")
show("SELECT DISTINCT city FROM customers")
conn.close()
▶ Output
== All products == id | name | price ---+-------------------+------ 1 | Paneer Tikka Wrap | 180.0 2 | Masala Chai | 40.0 3 | Veg Biryani | 220.0 4 | Filter Coffee | 50.0 5 | Gulab Jamun | 120.0 == Food only, cheapest first == name | price ------------------+------ Paneer Tikka Wrap | 180.0 Veg Biryani | 220.0 == Two most expensive items == name | price ------------------+------ Veg Biryani | 220.0 Paneer Tikka Wrap | 180.0 == Which cities do customers come from? == city ------ Pune Mumbai Nagpur None
What happened here: Read each query as an English sentence. The first grabs three columns from every product. The second filters to category = 'food' and sorts ascending by price, so the cheapest food lands on top. Notice the value 'food' goes in through a ? placeholder, never pasted into the string, the same safety habit that stops SQL injection. The third sorts descending and stops after two rows. The fourth uses DISTINCT to collapse the two Pune customers into a single Pune, and you can see None in that list, which is Python showing you a NULL, the database word for “no value here.” That None is your cue for the next section.
The NULL Trap
NULL means “unknown” or “not filled in.” It is not zero and it is not an empty string, it is the absence of a value. The trap is that NULL refuses to be compared with =. Asking “is this unknown value equal to that unknown value” has no sensible answer, so SQL says the comparison is neither true nor false, and the row is skipped. Anvi has no city, yet the query below that looks correct finds nobody.
📄 null_demo.py: why = NULL never works
# (same connection and show() helper as above)
print("== WRONG: WHERE city = NULL (returns nothing) ==")
show("SELECT name, city FROM customers WHERE city = NULL")
print("== RIGHT: WHERE city IS NULL ==")
show("SELECT name, city FROM customers WHERE city IS NULL")
print("== Customers who DO have a city ==")
show("SELECT name, city FROM customers WHERE city IS NOT NULL")
▶ Output
== WRONG: WHERE city = NULL (returns nothing) == (no rows) == RIGHT: WHERE city IS NULL == name | city -----+----- Anvi | None == Customers who DO have a city == name | city ---------+------- Aditi | Pune Anvay | Mumbai Aviraj | Pune Niranjan | Nagpur
What happened here: WHERE city = NULL returns zero rows even though Anvi clearly has no city, because = NULL is never true. The correct tool is IS NULL to find the blanks and IS NOT NULL to skip them. This one rule quietly breaks more beginner queries than anything else, so burn it in now: use IS NULL and IS NOT NULL, never = NULL.
JOINs: Connecting Two Tables
Here is where SQL earns its keep. Our orders table stores a customer_id and a product_id, just numbers, not names. That is on purpose, because you never want to store “Aditi” in a thousand order rows and then have to fix every one when she changes her name. A JOIN stitches the tables back together at query time, matching those id numbers to the real rows in customers and products.
The everyday picture: imagine two clipboards. One lists customers by number, the other lists orders by that same customer number. A join is you laying the clipboards side by side and drawing a line from each order to the customer it belongs to. The only real decision is what to do with a customer who has no order line to connect to, and that single choice is the whole difference between an INNER JOIN and a LEFT JOIN.
Tip: click the diagram to open it full screen, then use the zoom and pan controls for a closer look.
An INNER JOIN keeps only rows that have a match on both sides. A LEFT JOIN keeps every row from the left (first) table no matter what, and fills the right side with NULL when there is no match. Anvi, our customer with zero orders, is the test case that makes the difference visible.
📄 joins_demo.py: INNER keeps matches, LEFT keeps everyone
# (same connection and show() helper as above)
print("== INNER JOIN: every order with the customer and product names ==")
show("""
SELECT c.name AS customer, p.name AS product, o.quantity
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.id
JOIN products AS p ON o.product_id = p.id
ORDER BY o.id
""")
print("== LEFT JOIN: every customer, even those with zero orders ==")
show("""
SELECT c.name AS customer, o.id AS order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
ORDER BY c.name
""")
▶ Output
== INNER JOIN: every order with the customer and product names == customer | product | quantity ---------+-------------------+--------- Aditi | Paneer Tikka Wrap | 2 Aditi | Masala Chai | 1 Anvay | Veg Biryani | 1 Aviraj | Paneer Tikka Wrap | 1 Aviraj | Gulab Jamun | 3 Niranjan | Filter Coffee | 2 Aditi | Veg Biryani | 1 == LEFT JOIN: every customer, even those with zero orders == customer | order_id ---------+--------- Aditi | 1 Aditi | 2 Aditi | 7 Anvay | 3 Anvi | None Aviraj | 4 Aviraj | 5 Niranjan | 6
What happened here: The INNER JOIN turned raw id numbers into readable names by matching orders.customer_id to customers.id and orders.product_id to products.id. The short aliases o, c, and p save typing and make the ON conditions easy to read. Anvi never appears, because she has no order to match. The LEFT JOIN tells a different story: every customer shows up, and Anvi comes back with order_id set to None, the honest way of saying “this customer exists but has ordered nothing.” When someone asks “which customers have never bought anything,” a LEFT JOIN plus WHERE order_id IS NULL is the answer, and you now know both halves of that trick.
Aggregates: COUNT and SUM with GROUP BY
So far every query returned rows as they were. Aggregate functions squeeze many rows down into one number. COUNT tallies rows, SUM adds a column, and AVG, MIN, MAX do what their names say. On their own they collapse the whole table into a single line. Pair them with GROUP BY and you get one line per group instead, which is how you answer “units sold per product” or “revenue per city.” This is a first taste; the next post in the series goes deep on GROUP BY, HAVING, and window functions.
📄 agg_demo.py: one number, then one number per group
# (same connection and show() helper as above)
print("== One number: how many orders in total? ==")
show("SELECT COUNT(*) AS total_orders FROM orders")
print("== GROUP BY: units sold per product ==")
show("""
SELECT p.name AS product, SUM(o.quantity) AS units_sold
FROM orders AS o
JOIN products AS p ON o.product_id = p.id
GROUP BY p.name
ORDER BY units_sold DESC
""")
▶ Output
== One number: how many orders in total? == total_orders ------------ 7 == GROUP BY: units sold per product == product | units_sold ------------------+----------- Paneer Tikka Wrap | 3 Gulab Jamun | 3 Veg Biryani | 2 Filter Coffee | 2 Masala Chai | 1
What happened here: COUNT(*) with no grouping counted all seven order rows into a single number. The second query joins orders to products, then GROUP BY p.name makes SQLite bundle every order for the same product together and hand SUM(o.quantity) just that product’s rows. The result is a clean per-product tally, sorted so the best seller sits on top. That single pattern, join then group then aggregate, is the backbone of nearly every analytics query you will ever write.
The Python Bridge: Run SQL from Code
You may have noticed we have been running SQL from Python the whole time. That is the point: the SQL string is identical to what you would type into any database tool, and Python’s built-in sqlite3 module just hands it to the engine and gives you the rows back. Here is the pattern stripped to its essentials, answering a real business question, how much has each customer spent, in about ten lines.
📄 bridge.py: the same SQL, run from Python
import sqlite3
conn = sqlite3.connect("shop.db")
conn.row_factory = sqlite3.Row
# The SQL string is identical to what you would type in any SQL tool
sql = """
SELECT c.name AS customer, SUM(p.price * o.quantity) AS spent
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.id
JOIN products AS p ON o.product_id = p.id
GROUP BY c.name
ORDER BY spent DESC
"""
for row in conn.execute(sql):
print(f"{row['customer']:<10} spent Rs {row['spent']:.0f}")
conn.close()
▶ Output
Aditi spent Rs 620 Aviraj spent Rs 540 Anvay spent Rs 220 Niranjan spent Rs 100
What happened here: One SQL string did the heavy lifting: it joined all three tables, multiplied price by quantity for every order line, summed those per customer, and sorted the biggest spender to the top. Python just looped over the result. This is the bridge to the rest of the database chapter. When you outgrow raw strings, SQLAlchemy lets you work with Python objects instead, and the MySQL and PostgreSQL post shows the same queries against a real server, but every one of those tools speaks the SQL you just learned.
Common Mistakes
Mistake 1: Using = NULL instead of IS NULL
We saw it above and it is worth its own line because it fails silently. WHERE column = NULL always returns zero rows, never an error. If a filter that should match blanks keeps coming back empty, this is almost always why. Switch to IS NULL or IS NOT NULL.
Mistake 2: Expecting an INNER JOIN to show unmatched rows
A plain JOIN is an INNER JOIN, and it quietly drops any row without a match. If you join customers to orders with an inner join and wonder why a customer disappeared, it is because they have no orders. When you need to keep everyone, reach for LEFT JOIN.
Mistake 3: Pasting values into the query string
Writing f"... WHERE name = '{user_input}'" opens the door to SQL injection and is also slower. Always pass values through ? placeholders as a separate tuple, exactly like show("... WHERE category = ?", ("food",)) does above. The SQLite post covers the injection attack in full.
Best Practices
- DO name your columns explicitly in
SELECTinstead ofSELECT *, so your result does not change shape when someone adds a column later. - DO use table aliases (
orders AS o) and qualify columns (o.customer_id) once more than one table is in play, so nobody has to guess where a column lives. - DO use
IS NULLandIS NOT NULLfor blanks, and remember a plainJOINmeansINNER JOIN. - DO pass values with
?placeholders, never string formatting, for safety and speed alike. - DON’T reach for SQLite when many machines must write at once. That is the moment to move the same SQL to PostgreSQL or MySQL.
Conclusion
You now have the SQL basics that carry most day-to-day database work: SELECT to pick columns, WHERE to filter, ORDER BY and LIMIT to shape the result, DISTINCT to dedupe, IS NULL to handle blanks, INNER and LEFT JOIN to connect tables, and COUNT and SUM with GROUP BY to summarize. That is more than enough to answer real questions about real data, and you ran every one of those queries yourself against a database you built.
The best part is how far this travels. SQL is an ANSI standard, so these exact queries run on PostgreSQL and MySQL with tiny or zero changes, and that will still be true years from now. Next, the GROUP BY, CTEs, and window functions post turns these basics into the queries analysts write every week. For the whole path from beginner Python to the AI and ML chapters, start at the Python + AI/ML tutorial series home.
Practice Exercises
- Exercise 1: Write a query that returns only the drinks, sorted from most to least expensive, using the
productstable. - Exercise 2: Using a
LEFT JOINandWHERE ... IS NULL, list every customer who has never placed an order. You should get exactly one name back. - Exercise 3: Write a
GROUP BYquery that shows, for each city, how many customers live there. Decide what you want to happen to the customer whose city isNULL.
Frequently Asked Questions
What are the SQL basics every Python developer should know?
SELECT to choose columns, WHERE to filter rows, ORDER BY and LIMIT to shape results, DISTINCT to remove duplicates, IS NULL for blanks, INNER and LEFT JOIN to connect tables, and COUNT and SUM with GROUP BY to summarize. Those SQL basics cover most everyday queries.
What is the difference between INNER JOIN and LEFT JOIN?
An INNER JOIN keeps only rows that have a match on both sides. A LEFT JOIN keeps every row from the left table and fills the right side with NULL when there is no match, which is how you find records with no related rows.
Why does WHERE city = NULL return no rows?
NULL means unknown, and comparing anything to an unknown with = is never true. Use WHERE city IS NULL to find blanks and WHERE city IS NOT NULL to skip them.
Do I need to install a database to practice SQL?
No. SQLite ships inside Python through the sqlite3 module, so you can build tables and run every query in this post with zero setup. When you need concurrent writes at scale, move the same SQL to PostgreSQL or MySQL.
Is SQL going to become outdated?
Very unlikely. SQL is an ANSI standard that has stayed stable for decades and remains the second most requested skill after Python in data and backend roles at the time of writing. The queries you learn today keep working across engines and across years.
Interview Questions on SQL Basics
How interviewers actually probe this topic: real scenarios, with answers you can say out loud.
Q: What is the difference between WHERE and a JOIN condition?
A JOIN ... ON condition decides how rows from two tables are matched up, while WHERE filters the combined result after the matching is done. With an INNER JOIN the two feel interchangeable, but with a LEFT JOIN they behave very differently: a condition in the ON clause still lets unmatched left rows through as NULL, whereas the same condition in WHERE can filter those NULL rows back out. Keep join logic in ON and row filtering in WHERE.
Q: How would you find all customers who have never placed an order?
LEFT JOIN the customers table to orders on the customer id, then keep only the rows where the order id came back NULL: SELECT c.name FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL. The left join guarantees every customer appears, and the IS NULL filter isolates the ones with no matching order.
Q: Why can’t you use = to compare a value against NULL?
Because NULL means “unknown,” and SQL uses three-valued logic where a comparison to an unknown returns neither true nor false. Since WHERE only keeps rows where the condition is true, = NULL never keeps anything. The dedicated operators IS NULL and IS NOT NULL exist precisely to test for that unknown state.
Q: What does GROUP BY do, and what can you select alongside it?
GROUP BY collapses rows that share a value into one row per group so aggregate functions like COUNT and SUM run per group instead of over the whole table. The columns you select must either appear in the GROUP BY or be wrapped in an aggregate, otherwise the value is ambiguous. To filter on an aggregate result you use HAVING rather than WHERE.
Q: Why store customer_id in the orders table instead of the customer name?
Storing the id keeps each fact in exactly one place, which is the heart of normalization. The name lives once in the customers table, so a rename is a single update, and the orders table stays small and consistent. A JOIN reconnects the id to the name whenever you need to read it, which is cheap and safe compared to duplicating text across thousands of rows.
Go deeper: when you outgrow this post, SQL reference (SQLite) is the next stop.
Related Posts
Previous: Python Memory Management: References, Garbage Collection, and the Global Interpreter Lock (GIL)
Next: SQL GROUP BY, CTEs, and Window Functions, Explained
Series Home: Python + AI/ML Tutorial Series
No comment