SQLite IF EXISTS: DROP IF EXISTS, Checking If a Table or Row Exists, and IF Statements

Ninad Pathak

October 7, 2026

SQL Server’s IF EXISTS (SELECT …) fails in SQLite with near "IF": syntax error, because SQLite has no IF statement. There, IF EXISTS only appears in DROP statements, with IF NOT EXISTS as its partner on CREATE. Everything else it’s used for still has an SQLite answer, and in this article I’ll walk you through each one.

TLDR

DROP TABLE IF EXISTS t; and CREATE TABLE IF NOT EXISTS t (…); skip quietly instead of failing. Other schema objects take the same guards.
There’s no IF statement and no ALTER TABLE IF EXISTS.

Check if a table exists by querying sqlite_schema. For a column, use pragma_table_info().
Check if a row exists with SELECT EXISTS (SELECT 1 FROM … WHERE …). It returns 1 or 0.
Inside a query, CASE and iif() give you conditional logic.

What is IF EXISTS in SQLite?

IF EXISTS is an optional clause on DROP statements, and IF NOT EXISTS is its partner on CREATE statements. They turn “this object doesn’t exist” (or “already exists”) from an error into a no-op. The separate EXISTS operator works inside any query: it “evaluates to 1” if its subquery returns any rows and to 0 if it returns none (SQLite expressions).

SQLite IF EXISTS syntax

Statement	Guarded form
Drop a table	`DROP TABLE IF EXISTS name;`
Create a table	`CREATE TABLE IF NOT EXISTS name (…);`
Index	`CREATE INDEX IF NOT EXISTS …` / `DROP INDEX IF EXISTS …`
View	`CREATE VIEW IF NOT EXISTS …` / `DROP VIEW IF EXISTS …`
Trigger	`CREATE TRIGGER IF NOT EXISTS …` / `DROP TRIGGER IF EXISTS …`
Test inside a query	`EXISTS (SELECT 1 FROM … WHERE …)`

How to use IF EXISTS in SQLite

To show you how each form behaves, I will use a small shop database with customers and orders:

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, total REAL NOT NULL);
INSERT INTO customers (name, email) VALUES ('Ada', 'ada@example.com'), ('Linus', 'linus@example.com'), ('Grace', 'grace@example.com');
INSERT INTO orders (customer_id, total) VALUES (1, 20.0), (1, 35.5), (3, 99.9);

Step 1: Drop a table only if it exists

Without the guard, dropping a missing table is an error:

sqlite> DROP TABLE invoices;
Error: in prepare, no such table: invoices

With IF EXISTS, the same statement succeeds and does nothing:

sqlite> DROP TABLE IF EXISTS invoices;

Step 2: Create a table only if it doesn’t exist

CREATE TABLE fails when the table is already there. IF NOT EXISTS skips it, without checking whether the existing table has the same columns:

sqlite> CREATE TABLE customers (id INTEGER PRIMARY KEY);
Error: in prepare, table customers already exists
  CREATE TABLE customers (id INTEGER PRIMARY KEY);
               ^--- error here
sqlite> CREATE TABLE IF NOT EXISTS customers (id INTEGER PRIMARY KEY);

More on that in SQLite CREATE TABLE IF NOT EXISTS.

Step 3: Guard indexes, views and triggers the same way

sqlite> CREATE INDEX IF NOT EXISTS idx_orders_customer ON orders(customer_id); CREATE VIEW IF NOT EXISTS big_orders AS SELECT * FROM orders WHERE total > 50; DROP VIEW IF EXISTS old_report; DROP INDEX IF EXISTS idx_missing; DROP TRIGGER IF EXISTS trg_missing;
sqlite> SELECT type, name FROM sqlite_schema ORDER BY type, name;
type   name
-----  ----------------------------
index  idx_orders_customer
index  sqlite_autoindex_customers_1
table  customers
table  orders
view   big_orders

Examples

These run against the same database, one after another.

Check if a table exists

sqlite_schema lists every table, so EXISTS over it returns 1 or 0:

sqlite> SELECT EXISTS (SELECT 1 FROM sqlite_schema WHERE type = 'table' AND name = 'customers') AS customers_exists, EXISTS (SELECT 1 FROM sqlite_schema WHERE type = 'table' AND name = 'invoices') AS invoices_exists;
customers_exists  invoices_exists
----------------  ---------------
1                 0

The same check from Python:

import sqlite3

def table_exists(con, name):
    row = con.execute(
        "SELECT 1 FROM sqlite_schema WHERE type = 'table' AND name = ?", (name,)
    ).fetchone()
    return row is not None

con = sqlite3.connect("shop.db")
print("customers:", table_exists(con, "customers"))
print("invoices:", table_exists(con, "invoices"))
con.close()

$ python3 table_exists.py
customers: True
invoices: False

Check if a column exists

pragma_table_info() returns one row per column:

sqlite> SELECT EXISTS (SELECT 1 FROM pragma_table_info('customers') WHERE name = 'email') AS has_email, EXISTS (SELECT 1 FROM pragma_table_info('customers') WHERE name = 'phone') AS has_phone;
has_email  has_phone
---------  ---------
1          0

Check if a row exists

sqlite> SELECT EXISTS (SELECT 1 FROM customers WHERE email = 'grace@example.com') AS grace, EXISTS (SELECT 1 FROM customers WHERE email = 'ken@example.com') AS ken;
grace  ken
-----  ---
1      0

Filter rows with WHERE EXISTS

EXISTS with a subquery that refers to the outer row finds customers who have orders. NOT EXISTS finds the ones who don’t:

sqlite> SELECT name FROM customers AS c WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.id);
name
-----
Ada
Grace
sqlite> SELECT name FROM customers AS c WHERE NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.id);
name
-----
Linus

Write if/else logic with CASE or iif()

SQLite’s iif(X,Y,Z) is “logically equivalent to” CASE WHEN X THEN Y ELSE Z END, and if() is “an alternative spelling” for it (SQLite core functions):

sqlite> SELECT name, iif(EXISTS (SELECT 1 FROM orders WHERE customer_id = c.id), 'has orders', 'no orders') AS status, CASE WHEN id = 1 THEN 'first' ELSE 'later' END AS signup FROM customers AS c;
name   status      signup
-----  ----------  ------
Ada    has orders  first
Linus  no orders   later
Grace  has orders  later

sqlite> SELECT if(1 > 2, 'yes', 'no') AS answer;
answer
------
no

The if() spelling needs SQLite 3.48.0 or later. On older versions, use iif().

Insert a row only if it doesn’t exist

INSERT … SELECT … WHERE NOT EXISTS inserts Ken the first time and nothing the second:

sqlite> INSERT INTO customers (name, email) SELECT 'Ken', 'ken@example.com' WHERE NOT EXISTS (SELECT 1 FROM customers WHERE email = 'ken@example.com'); SELECT changes() AS inserted;
inserted
--------
1
sqlite> INSERT INTO customers (name, email) SELECT 'Ken', 'ken@example.com' WHERE NOT EXISTS (SELECT 1 FROM customers WHERE email = 'ken@example.com'); SELECT changes() AS inserted;
inserted
--------
0

If a UNIQUE constraint already covers the column, INSERT OR IGNORE does the same job. Ada’s email is taken, so nothing is inserted:

sqlite> INSERT OR IGNORE INTO customers (name, email) VALUES ('Ada again', 'ada@example.com'); SELECT changes() AS inserted;
inserted
--------
0

To insert or else update, see SQLite UPSERT.

Update or delete only if the row exists

You don’t need a guard. UPDATE and DELETE don’t fail when no row matches. They change nothing:

sqlite> UPDATE customers SET name = 'Nobody' WHERE id = 99; SELECT changes() AS updated;
updated
-------
0
sqlite> DELETE FROM customers WHERE id = 99; SELECT changes() AS deleted;
deleted
-------
0

Edge cases

No ALTER TABLE IF EXISTS: SQLite rejects it. Check sqlite_schema from your code first:

sqlite> ALTER TABLE IF EXISTS customers ADD COLUMN phone TEXT;
Error: in prepare, near "EXISTS": syntax error
  ALTER TABLE IF EXISTS customers ADD COLUMN phone TEXT;
                 ^--- error here

No IF statement: T-SQL’s IF EXISTS (…) … is a syntax error. Move the condition into a CASE expression, or branch in your application code:

sqlite> IF EXISTS (SELECT 1 FROM customers WHERE id = 1) SELECT 'found';
Error: in prepare, near "IF": syntax error
  IF EXISTS (SELECT 1 FROM customers WHERE id = 1) SELECT 'found';
  ^--- error here

CREATE TABLE IF NOT EXISTS doesn’t compare columns: an old table with a different schema stays as it is.

Conclusion

Use IF EXISTS and IF NOT EXISTS for schema scripts you want to re-run safely. For everything else, use the EXISTS operator. Point it at sqlite_schema for tables and at a subquery for rows.