Skip to main content
Advanced query features for complex data retrieval.

​
Common Table Expressions (CTE)

CTEs create temporary named result sets for use in queries. 
:CTE active_users AS (GET User WHERE active = true)
DatabaseOutput
PostgreSQLWITH active_users AS (SELECT * FROM users WHERE active = true)
MySQLWITH active_users AS (SELECT * FROM users WHERE active = true)

​
Use Cases

CTEs are useful for:
  • Breaking complex queries into readable parts
  • Reusing the same subquery multiple times
  • Recursive queries

​
Subqueries

Filter using results from another query. 
:SUBQUERY id IN (GET User WHERE active = true)
DatabaseOutput
PostgreSQLSELECT * FROM ... WHERE id IN (SELECT id FROM users WHERE active = true)

​
Example: Users with Orders

:SUBQUERY user_id IN (GET Order WHERE total > 100)

​
EXISTS

Check if a subquery returns any results. 
:EXISTS (GET User WHERE email = "[email protected]")
DatabaseOutput
PostgreSQLSELECT EXISTS(SELECT 1 FROM users WHERE email = '[email protected]')
MongoDBdb.users.countDocuments({ email: '[email protected]' }, { limit: 1 })

​
Set Operations

Combine results from multiple queries.

​
UNION

Combine results, removing duplicates. 
:UNION (GET User WHERE age > 50) (GET User WHERE role = "premium")
DatabaseOutput
PostgreSQL(SELECT * FROM users WHERE age > 50) UNION (SELECT * FROM users WHERE role = 'premium')

​
UNION ALL

Combine results, keeping duplicates. 
:UNION ALL (GET User WHERE department = "sales") (GET User WHERE department = "marketing")

​
INTERSECT

Return only rows that appear in both queries. 
:INTERSECT (GET User WHERE age > 30) (GET User WHERE active = true)
DatabaseOutput
PostgreSQL(SELECT * FROM users WHERE age > 30) INTERSECT (SELECT * FROM users WHERE active = true)

​
EXCEPT

Return rows from first query that don’t appear in second. 
:EXCEPT (GET User WHERE active = true) (GET User WHERE role = "banned")
DatabaseOutput
PostgreSQL(SELECT * FROM users WHERE active = true) EXCEPT (SELECT * FROM users WHERE role = 'banned')

​
CASE Expressions

Conditional logic within queries. CASE must be used within GET expressions. 
:GET User WITH CASE WHEN age > 25 THEN "adult" ELSE "minor" END AS category
DatabaseOutput
PostgreSQLSELECT *, CASE WHEN age > 25 THEN 'adult' ELSE 'minor' END AS category FROM users

​
Multiple Conditions

:GET User WITH CASE 
  WHEN age < 18 THEN "minor" 
  WHEN age < 65 THEN "adult" 
  ELSE "senior" 
END AS age_group

​
CASE in WHERE

:GET Order WHERE CASE WHEN total > 1000 THEN "large" ELSE "small" END = "large"

​
Database Support

FeaturePostgreSQLMySQLMongoDB
CTEYesYes (8.0+)No
SUBQUERYYesYesVia aggregation
EXISTSYesYesVia count
UNIONYesYesVia $unionWith
UNION ALLYesYesVia $unionWith
INTERSECTYesYes (8.0+)Via aggregation
EXCEPTYesYes (8.0+)Via aggregation
CASEYesYesVia $cond

​
Complete Examples

​
Active Premium Users with Order Stats

:CTE premium AS (GET User WHERE role = "premium" AND active = true)

​
Users Without Recent Orders

:EXCEPT (GET User WHERE active = true) (SUBQUERY user_id IN (GET Order WHERE created_at > "2024-01-01"))

​
Categorized Products

:GET Product WITH 
  name, 
  price, 
  CASE 
    WHEN price < 10 THEN "budget" 
    WHEN price < 100 THEN "standard" 
    ELSE "premium" 
  END AS tier

​
Next Steps

Window Functions

ROW NUMBER, RANK, LAG, LEAD

Joins

Combine multiple tables