Section 3: CQL Fundamentals

Complete CQL Syntax Guide

Master Cassandra Query Language from basics to advanced! DDL, DML, queries, data types, and real-world examples with copy-paste code.

Home › Section 3: CQL Fundamentals › CQL Syntax

💻 What is CQL?

CQL = Cassandra Query Language

CQL (Cassandra Query Language) is the primary interface for interacting with Cassandra. It looks like SQL, but remember: Cassandra is NOT a relational database!

🎯 Think of CQL Like This:

Imagine you're organizing a huge library with millions of books. SQL is like organizing by the Dewey Decimal System - everything has a specific place based on relationships. CQL is like organizing books by which shelf they're on - fast to find if you know the shelf number, but you can't easily search "all books by author X across all shelves."

Key Idea: CQL is designed for speed and scale, not flexibility. You need to know what you're looking for!

✅ What CQL IS

  • SQL-like syntax - If you know SQL, you'll feel at home
  • Easy to learn - Most commands work like you'd expect
  • Powerful for NoSQL - Built for distributed data
  • Optimized for speed - Direct partition lookups are lightning fast

❌ What CQL is NOT

  • NOT full SQL - No JOINs! Each table is independent
  • NOT ACID transactions - Eventually consistent by default
  • NOT for complex aggregations - No SUM() across partitions
  • NOT normalized - Denormalization is normal!

CQL vs SQL: A Beginner-Friendly Comparison

Feature CQL (Cassandra) SQL (MySQL/Postgres)
SELECT ✅ Yes, but MUST have partition key ✅ Yes, can query any column
INSERT ✅ Same as SQL ✅ Same as CQL
UPDATE ✅ Same as SQL ✅ Same as CQL
DELETE ✅ Same as SQL ✅ Same as CQL
JOINs ❌ NO! Store denormalized data ✅ Yes, JOIN tables together
WHERE ⚠️ Only on primary key columns ✅ On any column with index

Bottom Line: CQL looks like SQL for basic operations (INSERT, SELECT, UPDATE, DELETE), but WHERE clauses are restricted. You MUST design your tables based on your queries!

💻 Try CQL Right Now - Live Console!

Practice CQL Without Installing Anything!

This is a fully functional CQL simulator that runs in your browser. Type queries, see results, and learn by doing! The best way to learn is to practice, so try every example you see below!

🚀 CQL Live Console Type your queries below ↓
Welcome to CQL Live Console! Type a query above and click "Run Query"
💡 Try these commands: CREATE TABLE, INSERT, SELECT, UPDATE, DELETE

Quick Tips for Using the Console

  • Multiple statements: Separate with semicolons (;)
  • Auto-UUID: Use uuid() to generate unique IDs automatically
  • Data persists: Your data stays during the session (refresh to reset)
  • Try buttons: Click "Try This Code ▶" buttons throughout the page to load examples!
  • Errors?: Read the error message - it tells you what's wrong!
⚡ QUICK EXAMPLES - Click to Load Working Code: (12 Real-World Scenarios)

💡 Pro Tip: Each example is a complete, working scenario from real-world applications. Click any button to load instant code, then hit "Run Query" to see it in action!

📖 CQL Basics

Comments

-- Single line comment

/* Multi-line comment
   can span multiple lines
   like this */

SELECT * FROM users; -- inline comment

Case Sensitivity

  • Keywords: Case-insensitive (SELECT = select = SeLeCt)
  • Identifiers (unquoted): Case-insensitive, stored lowercase
  • Identifiers (quoted): Case-sensitive
  • String values: Always case-sensitive
-- These are equivalent:
SELECT * FROM Users;
select * from users;
Select * From USERS;

-- But this is different (case-sensitive):
SELECT * FROM "Users"; -- looks for table "Users" exactly

Statement Terminator

-- Statements end with semicolon ;
SELECT * FROM users;
INSERT INTO users (id, name) VALUES (uuid(), 'Alice');

🔤 Data Types

Text & String

TEXT -- UTF-8 string (unlimited)
VARCHAR -- Alias for TEXT
ASCII -- US-ASCII only

Use TEXT for most strings!

Numbers

INT -- 32-bit signed (-2B to 2B)
BIGINT -- 64-bit signed
SMALLINT -- 16-bit signed
TINYINT -- 8-bit signed
FLOAT -- 32-bit floating point
DOUBLE -- 64-bit floating point
DECIMAL -- Variable precision

Date & Time

TIMESTAMP -- Date + time (milliseconds)
DATE -- Just date (YYYY-MM-DD)
TIME -- Just time (nanoseconds)
DURATION -- Time interval

UUID & Boolean

UUID -- Universal unique ID
TIMEUUID -- Time-based UUID
BOOLEAN -- true/false

Binary & Special

BLOB -- Binary data
INET -- IP address (v4/v6)
COUNTER -- Distributed counter

Collections

SET<type> -- Unique values
LIST<type> -- Ordered, duplicates OK
MAP<key,val> -- Key-value pairs

Common Type Choices

  • IDs: Use UUID or TIMEUUID
  • Names/Text: Use TEXT
  • Prices: Use DECIMAL (not FLOAT!)
  • Timestamps: Use TIMESTAMP
  • Counts: Use INT or BIGINT

🏗️ Keyspace Operations

CREATE KEYSPACE

Create Keyspace
CREATE KEYSPACE my_app
WITH replication = {
  'class': 'SimpleStrategy',
  'replication_factor': 3
}
AND durable_writes = true;

-- Production (multiple datacenters):
CREATE KEYSPACE my_app
WITH replication = {
  'class': 'NetworkTopologyStrategy',
  'datacenter1': 3,
  'datacenter2': 2
};

USE KEYSPACE

USE my_app;

-- Now all operations are in my_app keyspace
SELECT * FROM users;

ALTER & DROP KEYSPACE

-- Change replication factor:
ALTER KEYSPACE my_app
WITH replication = {
  'class': 'SimpleStrategy',
  'replication_factor': 5
};

-- Delete keyspace (CAREFUL!):
DROP KEYSPACE my_app;

📊 Table Operations

CREATE TABLE

Basic Table
CREATE TABLE users (
  user_id UUID PRIMARY KEY,
  username TEXT,
  email TEXT,
  created_at TIMESTAMP
);
Composite Primary Key
CREATE TABLE user_posts (
  user_id UUID,
  post_id TIMEUUID,
  content TEXT,
  likes_count INT,
  PRIMARY KEY (user_id, post_id)
) WITH CLUSTERING ORDER BY (post_id DESC);

Primary Key Syntax

-- Simple primary key (partition key only):
PRIMARY KEY (user_id)

-- Composite key (partition + clustering):
PRIMARY KEY (user_id, post_id)

-- Compound partition key:
PRIMARY KEY ((user_id, category), post_id)

ALTER TABLE

-- Add column:
ALTER TABLE users ADD phone TEXT;

-- Drop column:
ALTER TABLE users DROP phone;

-- Rename column:
ALTER TABLE users RENAME username TO user_name;

DROP & TRUNCATE TABLE

-- Delete all data (keeps structure):
TRUNCATE users;

-- Delete table entirely:
DROP TABLE users;

✏️ CRUD Operations

INSERT

Insert Data
-- Basic insert:
INSERT INTO users (user_id, username, email)
VALUES (uuid(), 'alice', 'alice@example.com');

-- With TTL (time to live in seconds):
INSERT INTO sessions (session_id, user_id)
VALUES (uuid(), uuid())
USING TTL 3600; -- expires in 1 hour

-- With timestamp (microseconds since epoch):
INSERT INTO events (event_id, data)
VALUES (uuid(), 'clicked')
USING TIMESTAMP 1609459200000000;

UPDATE

-- Basic update:
UPDATE users
SET email = 'newemail@example.com'
WHERE user_id = ?;

-- Update with TTL:
UPDATE sessions USING TTL 7200
SET last_active = toTimestamp(now())
WHERE session_id = ?;

-- Conditional update (lightweight transaction):
UPDATE users
SET email = 'new@example.com'
WHERE user_id = ?
IF email = 'old@example.com'; -- Only update if email matches

DELETE

-- Delete entire row:
DELETE FROM users WHERE user_id = ?;

-- Delete specific columns:
DELETE email, phone FROM users WHERE user_id = ?;

-- Delete with condition:
DELETE FROM users
WHERE user_id = ?
IF status = 'inactive';

SELECT

-- Select all columns:
SELECT * FROM users WHERE user_id = ?;

-- Select specific columns:
SELECT username, email FROM users WHERE user_id = ?;

-- With LIMIT:
SELECT * FROM user_posts
WHERE user_id = ?
LIMIT 10;

-- Count rows:
SELECT COUNT(*) FROM users WHERE user_id = ?;

🔍 Query Syntax

WHERE Clause Rules

⚠️ Critical Rules

  • MUST include partition key in WHERE clause
  • Clustering columns must be in PRIMARY KEY order
  • Can use ranges (>, <, >=, <=) on last clustering column only
  • IN clause allowed on partition/clustering keys
  • ALLOW FILTERING bypasses rules but is SLOW
-- ✅ GOOD: Has partition key
SELECT * FROM user_posts
WHERE user_id = ? AND post_id > ?;

-- ❌ BAD: No partition key
SELECT * FROM user_posts
WHERE post_id = ?; -- ERROR!

-- ⚠️ SLOW: Uses ALLOW FILTERING
SELECT * FROM user_posts
WHERE likes_count > 100
ALLOW FILTERING; -- Scans ALL partitions!

Operators

Comparison

  • = equals
  • != not equals
  • > greater than
  • < less than
  • >= greater or equal
  • <= less or equal

Set Operations

  • IN matches list
  • CONTAINS in collection
  • CONTAINS KEY map key exists

ORDER BY

-- Order by clustering column:
SELECT * FROM user_posts
WHERE user_id = ?
ORDER BY post_id DESC; -- or ASC

-- Note: ORDER BY only works on clustering columns!
-- Direction must match CLUSTERING ORDER BY in table def

📦 Collections

SET (Unique Values)

-- Create table with SET:
CREATE TABLE users (
  user_id UUID PRIMARY KEY,
  interests SET<TEXT>
);

-- Insert SET:
INSERT INTO users (user_id, interests)
VALUES (uuid(), {'music', 'coding', 'gaming'});

-- Add to SET:
UPDATE users
SET interests = interests + {'reading'}
WHERE user_id = ?;

-- Remove from SET:
UPDATE users
SET interests = interests - {'gaming'}
WHERE user_id = ?;

LIST (Ordered, Duplicates OK)

-- Create table with LIST:
CREATE TABLE playlists (
  playlist_id UUID PRIMARY KEY,
  songs LIST<TEXT>
);

-- Insert LIST:
INSERT INTO playlists (playlist_id, songs)
VALUES (uuid(), ['song1', 'song2', 'song3']);

-- Append to LIST:
UPDATE playlists
SET songs = songs + ['song4']
WHERE playlist_id = ?;

-- Prepend to LIST:
UPDATE playlists
SET songs = ['intro'] + songs
WHERE playlist_id = ?;

MAP (Key-Value Pairs)

-- Create table with MAP:
CREATE TABLE users (
  user_id UUID PRIMARY KEY,
  settings MAP<TEXT, TEXT>
);

-- Insert MAP:
INSERT INTO users (user_id, settings)
VALUES (uuid(), {'theme': 'dark', 'lang': 'en'});

-- Update MAP entry:
UPDATE users
SET settings['theme'] = 'light'
WHERE user_id = ?;

-- Delete MAP entry:
DELETE settings['lang']
FROM users
WHERE user_id = ?;

⚠️ Collection Limits

  • Keep collections < 100 items
  • Entire collection loaded on read
  • Large collections hurt performance
  • Consider separate tables for large datasets

⚡ Advanced Features

Batches

-- LOGGED batch (atomic, slower):
BEGIN BATCH
  INSERT INTO users (user_id, name) VALUES (?, 'Alice');
  INSERT INTO user_emails (email, user_id) VALUES ('alice@ex.com', ?);
APPLY BATCH;

-- UNLOGGED batch (faster, no atomicity):
BEGIN UNLOGGED BATCH
  INSERT INTO logs (log_id, msg) VALUES (uuid(), 'msg1');
  INSERT INTO logs (log_id, msg) VALUES (uuid(), 'msg2');
APPLY BATCH;

Don't use batches for performance! Use them only for atomicity across partitions.

Lightweight Transactions (LWT)

-- Insert only if not exists:
INSERT INTO users (user_id, username)
VALUES (?, 'alice')
IF NOT EXISTS;

-- Update with condition:
UPDATE accounts
SET balance = balance - 100
WHERE account_id = ?
IF balance >= 100;

LWT are 10x slower! Use only when you need strong consistency.

User-Defined Types (UDT)

-- Create UDT:
CREATE TYPE address (
  street TEXT,
  city TEXT,
  zip TEXT
);

-- Use in table:
CREATE TABLE users (
  user_id UUID PRIMARY KEY,
  home_address FROZEN<address>
);

-- Insert UDT:
INSERT INTO users (user_id, home_address)
VALUES (uuid(), {
  street: '123 Main St',
  city: 'NYC',
  zip: '10001'
});

✅ Best Practices

✅ DO These

  • Always include partition key in WHERE
  • Use prepared statements
  • Set appropriate TTL for time-series
  • Use TIMEUUID for time-based IDs
  • Keep collections small (< 100 items)
  • Use UNLOGGED batches for same partition

❌ DON'T Do These

  • Never use ALLOW FILTERING in production
  • Don't use LOGGED batches for performance
  • Don't store large BLOBs (> 10MB)
  • Don't use LWT unless necessary
  • Don't query without partition key
  • Don't use collections for large datasets

Quick Reference

-- Complete example:
CREATE TABLE sensor_readings (
  sensor_id UUID,
  reading_time TIMESTAMP,
  temperature DECIMAL,
  humidity DECIMAL,
  PRIMARY KEY (sensor_id, reading_time)
) WITH CLUSTERING ORDER BY (reading_time DESC)
  AND default_time_to_live = 2592000; -- 30 days

-- Insert with TTL:
INSERT INTO sensor_readings (sensor_id, reading_time, temperature, humidity)
VALUES (?, toTimestamp(now()), 72.5, 45.2)
USING TTL 86400;

-- Query recent readings:
SELECT * FROM sensor_readings
WHERE sensor_id = ?
  AND reading_time >= '2025-01-01'
LIMIT 100;
Previous
CQL- Intro
Advertisement

Responsive Ad