DEV Community

Cover image for Subqueries & Views in SQLite: Writing Smarter, Cleaner Queries
Athreya aka Maneshwar
Athreya aka Maneshwar

Posted on

Subqueries & Views in SQLite: Writing Smarter, Cleaner Queries

#webdev #programming #database #architecture

Hello, I'm Maneshwar. I'm building git-lrc, an AI code reviewer that runs on every commit. It is free, unlimited, and source-available on Github. Star Us to help devs discover the project. Do give it a try and share your feedback for improving the product.

In the previous discussion, we explored how PRAGMA gives you control over SQLite’s internal behavior.

Now, let’s see how you actually structure smarter queries using subqueries and views.

These aren’t just SQL features.

They’re tools that help you write cleaner logic, reduce duplication, and make your database easier to work with.

Subqueries: Queries Inside Queries

At its core, a basic SQL query looks like this:

SELECT x FROM y WHERE z;
Enter fullscreen mode Exit fullscreen mode
  • x → columns
  • y → tables
  • z → condition

Now here’s where things get interesting:
That z (the condition) can itself contain another query.

That’s a subquery.

A Simple Subquery Example 

SELECT name 
FROM Students 
WHERE sid IN (
    SELECT sid 
    FROM Admitted_to 
    WHERE doj = 'Jan 01, 2000'
);
Enter fullscreen mode Exit fullscreen mode

What’s happening here?

  • Inner query → finds student IDs admitted on a specific date
  • Outer query → fetches names of those students

The inner query runs first, then feeds results into the outer query.

Where You Can Use Subqueries

Subqueries aren’t limited to WHERE. You can use them in:

  • WHERE clause (most common)
  • FROM clause (as derived tables)
  • HAVING clause (with grouped data)

They act like temporary datasets inside your query.

Correlated Subqueries: When Things Get Dynamic

Now we step into more advanced territory.

A correlated subquery is one that depends on the outer query.

Example: 

SELECT name 
FROM Students 
WHERE EXISTS (
    SELECT * 
    FROM Admitted_to 
    WHERE sid = Students.sid 
    AND doj = 'Jan 01, 2000'
);
Enter fullscreen mode Exit fullscreen mode

What’s different here?

  • The inner query references Students.sid
  • That means it cannot run independently
  • It depends on each row from the outer query

How Correlated Subqueries Actually Execute

This is where many people misunderstand what’s happening.

For each row in Students:

  1. SQLite takes that row’s sid
  2. Substitutes it into the subquery
  3. Executes the subquery
  4. Decides whether to include that row

So instead of running once, the subquery runs multiple times.

This makes correlated subqueries:

  • Powerful
  • But potentially slower

When to Use Subqueries (and When Not To)

Use them when:

  • You want clear, readable logic
  • You need intermediate filtering
  • You’re avoiding complex joins

Avoid them when:

  • Performance is critical
  • A join can do the same job more efficiently

Views: Virtual Tables That Simplify Everything

If subqueries are about embedding logic, views are about reusing it.

A view is basically a saved query that behaves like a table.

But here’s the key detail:

A view does not store data
It stores only the query definition

Creating a View 

CREATE VIEW view1 AS 
SELECT name, sid 
FROM Students;
Enter fullscreen mode Exit fullscreen mode

This doesn’t create a new table.

Instead, SQLite stores the query definition internally.

Using a View

Once created, you can query it like a normal table:

SELECT name 
FROM view1 
WHERE sid = 1001;
Enter fullscreen mode Exit fullscreen mode

Behind the scenes:

  • SQLite runs the original query
  • Then applies your new query on top

Why Views Exist (And Why You Should Care)

Views solve real problems:

1. Simplify Complex Queries

Instead of repeating a long query everywhere:

  • Write it once as a view
  • Reuse it everywhere

2. Hide Complexity

You can expose only what users need:

  • Hide joins
  • Hide sensitive columns
  • Present clean datasets

3. Provide Schema Independence

If your base table changes:

  • Your view may still work unchanged
  • Applications using the view stay stable

Temporary Views

SQLite also allows temporary views:

CREATE TEMP VIEW temp_view AS 
SELECT name FROM Students;
Enter fullscreen mode Exit fullscreen mode
  • Exists only during the session
  • Automatically deleted when connection closes

Useful for:

  • Testing
  • Intermediate processing
  • Session-based logic

The Catch: Views in SQLite Are Read-Only

Unlike some databases, SQLite does not allow direct updates on views.

So these won’t work:

INSERT INTO view1 ...
UPDATE view1 ...
DELETE FROM view1 ...
Enter fullscreen mode Exit fullscreen mode

If you need updates:

  • You must use triggers on the view
  • Those triggers then modify base tables

git-lrc
*AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.

git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.*

Any feedback or contributors are welcome! It's online, source-available, and ready for anyone to use.

⭐ Star it on GitHub:

GitHub logo HexmosTech / git-lrc

Free, Unlimited AI Code Reviews That Run on Commit



AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.

git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.

See It In Action

See git-lrc catch serious security issues such as leaked credentials, expensive cloud operations, and sensitive material in log statements

git-lrc-intro-60s.mp4

Why

  • 🤖 AI agents silently break things. Code removed. Logic changed. Edge cases gone. You won't notice until production.
  • 🔍 Catch it before it ships. AI-powered inline comments show you exactly what changed and what looks wrong.
  • 🔁 Build a…
View on GitHub

Top comments (0)