Skip to content
Search

Structured Query Language

lastmod 2026-04-22created 2014-06-1482 edits

SQL (Structured Query Language) is a computer language designed for retrieving and managing data, creating and modifying database schemas, and managing access control to database objects in relational database management systems. SQL is the standard interactive programming language for getting or updating information from a database. Many database-related programs adopt SQL as a standard.

Categories

Projects

  • Oxy - Open-source framework for building SQL agents and data automation
  • SQLX - Open-source Rust-based SQL toolkit
  • FilterQL - Lightweight query language for filtering structured data
  • Data-peek - Minimal, fast desktop SQL client with AI-powered SQL queries
  • ggsql - A grammar of graphics for SQL

Linter/Formatter

SQL Examples

Functions

DDL (Data Definition Language)

A language for defining databases; it determines the overall skeleton of the data, such as creating, modifying, and deleting data. It is used to create databases, tables, and so on.

Includes CREATE, ALTER, DROP, TRUNCATE, etc.

DML (Data Manipulation Language)

A language that database users use to actually process stored data through application programs or query languages. It provides the interface between database users and the database management system.

Includes SELECT, INSERT, UPDATE, DELETE, etc.

DCL (Data Control Language)

A language for controlling data. It is used to define data security, integrity, recovery, permissions, and so on. It is sometimes split into TCL (Transaction Control Language) and DCL.

Includes GRANT, REVOKE, COMMIT, ROLLBACK, etc.

Create 

CREATE TABLE IF NOT EXISTS {table_name} ({column_name_1} TEXT PRIMARY KEY, {column_name_2} TEXT, {column_name_3} TEXT);

Insert 

INSERT INTO {table_name}({column_name_1}, {column_name_2}, {column_name_3}) VALUES('{column_value_1}', '{column_value_2}', '{column_value_3}');

Select

A simple Select statement is used as follows. 

SELECT {column_name_1} FROM {table_name} WHERE [조건];

To remove duplicate column values, add DISTINCT as shown below.
(For reference, DISTINCT means separate, clearly different, etc.) 

SELECT DISTINCT {column_name_1} FROM {table_name} WHERE [조건];

String condition search 

SELECT {column_name_1} FROM {table_name} WHERE {column_name_1} LIKE "%@gmail.com";

In PostgreSQL, strings must use single quotes (') rather than double quotes ("). 

select * from graph_tasks where name like 'CENTRAL::GRAPH::Company::Task';

Numeric range search 

SELECT {column_name_1} FROM {table_name} WHERE {column_name_1} BETWEEN 10 AND 20;

Ascending/descending sort and number of rows returned 

select * from a4y_state where uid=1151 order by time desc limit 1;

LIKE comparison

  • % - Matches a string of any length
  • _ - Exactly one character

Update

The UPDATE statement has the following syntax: 

UPDATE {table_name} SET {column_name_1} = value [, {column_name_2} = value ...] [WHERE condition]

Example: 

UPDATE shboard SET C1 = 1 WHERE C2 = 'a'

DELETE 

DELETE FROM pies WHERE flavor='Lemon Meringue';

GROUP BY

See SQL:GroupBy for details.

DISTINCT

HAVING

JOIN

See SQL:Join for details. Extends table columns by attaching horizontally.

Subquery

A query nested inside another query, letting you retrieve data from one table based on values in another table

UNION

UNION can combine the results of two SELECTs. (JOIN attaches horizontally, UNION attaches vertically)

Plain union removes duplicates, union all includes duplicates <- (needs verification) 

select group_uid as domain_uid, user_uid, role_uid, 0 as category from recc_group_member
union all
select project_uid as domain_uid, user_uid, role_uid, 1 as category from recc_project_member;

Complex example 

select
    project_uid,
    permission_uid,
    user_uid,
    rank
from
    (
        select
            p.uid as project_uid,
            gm.permission_uid AS permission_uid,
            gm.user_uid AS user_uid,
            0 as rank
        from
            recc_project p
            JOIN recc_group_member gm ON gm.group_uid=p.group_uid
    union all
        select
            p.uid as project_uid,
            pm.permission_uid AS permission_uid,
            pm.user_uid AS user_uid,
            1 as rank
        from
            recc_project p
            JOIN recc_project_member pm ON pm.project_uid=p.uid
    ) aa
where
    permission_uid >= 1
;

FOR UPDATE SKIP LOCKED

The select for update statement has an option called skip locked, which skips selecting rows when another session holds a lock on them.

Be careful: because it does not fetch data even when the data actually exists, you might mistakenly assume no rows satisfy the condition and write incorrect logic.

The ​FOR UPDATE SKIP LOCKED clause is mainly used to implement a multi-consumer queue.

Concatenating strings

To convert "\r\n" characters to "/", since \r = char(13) and \n = char(10): 

select replace(replace(name, char(13), ''), char(10), '/') as name
from scrap_grade;

How to make the two "\r\n" characters ( char(13) and char(10)) into a single string:

MySQL

Use the CONCAT function. 

SELECT REPLACE(aaa, CONCAT(CHAR(13), CHAR(10)), '/') AS replaced_text
FROM your_table;

SQL Server

Use the + operator. 

SELECT REPLACE(aaa, CHAR(13) + CHAR(10), '/') AS replaced_text
FROM your_table;

PostgreSQL and SQLite

Use the string concatenation operator ||. 

SELECT REPLACE(aaa, CHR(13) || CHR(10), '/') AS replaced_text
FROM your_table;

SQL NULLs are weird

NULL values are handled peculiarly in SQL. A column with a UNIQUE constraint can have multiple NULL values.

  • This is because each NULL value is treated as an independent value distinct from other NULLs
  • SQLite, Postgres, and MySQL all behave the same way. 
select '' = '';    -- Returns 1 (true) 빈 문자열은 같음   
select 1 = 1;      -- Returns 1 (true) 숫자는 같음   
select 1 = 0;      -- Returns 0 (false) 숫자가 다름   
select null = null; -- Returns NULL (null) 응?
    • Because NULL is a placeholder representing an "unknown value", two unknown values are not considered equal
    • The IS operator lets you check NULL's identity. For example, null is null returns TRUE.
  • On uniqueness
    • When a column with a UNIQUE constraint contains NULL values, the NULLs are treated as distinct from each other and do not violate the uniqueness constraint.
    • For example, ('[email protected]', NULL) and ('[email protected]', NULL) are treated as different rows.
  • Why NULL is handled this way
    • SQLite and other SQL-compatible databases implement it this way to handle NULL consistently with other databases. The SQL standard document suggests that NULLs should be distinct everywhere, but in practice most SQL engines do not treat NULLs as distinct in SELECT DISTINCT or UNION.
  • How to guarantee uniqueness
    • Use a generated column
      • You can mitigate the problem by generating a column that always has a deterministic non-NULL value. For example, use COALESCE(deleted_at, '1970-01-01') to replace NULL values.
      • This approach adds an extra field to the table, which can take up space.
    • Use a partial index
      • You can guarantee uniqueness by creating a partial index on email only where deleted_at is NULL.
      • A partial index does not widen the table, takes less space, and does not raise errors when the same pair of records is deleted repeatedly.
  • Update
    • Oracle treats the empty string as NULL.
  • Conclusion
    • It is invisible when using an ORM, but SQL's peculiar handling of NULL can cause confusion. The SQL standard document is not publicly available and can only be obtained by paying for it.

SQL query execution order (optimization)

Anti-patterns to avoid

  • SQL anti-patterns to avoid | GeekNews
  • Overly complex CASE WHEN statements
  • Using functions on indexed columns
  • Using SELECT * in views
  • Overusing DISTINCT to fix duplicates
  • Nested views (Excessive View Layer Stacking)
  • Excessively deep subqueries

Problems with archived_at column-based soft deletes for data recovery and regulatory compliance

Soft deletes based on an archived_at column are often used for data recovery and regulatory compliance, but complexity and inefficiency grow over time

  • This approach complicates queries, indexes, migrations, and restore logic, and since most archived data is never read again, it puts unnecessary load on the database
  • Alternatives proposed are application event-based archiving, trigger-based archiving, and WAL (Change Data Capture)-based archiving
  • Each approach differs in operational complexity, infrastructure requirements, and ease of restoration; WAL-based archiving in particular requires integration with external systems such as Kafka
  • For a new project, the trigger-based approach is the most balanced choice in terms of simplicity and maintainability

Online tools

AI Query | Generate SQL Queries with AI in Seconds
https://aiquery.co/
AI-powered SQL builder
  • A tool that automatically generates queries from simple English sentences
  • For best results, write at least the table and column names, and choose one of the 4 operations CREATE/READ/UPDATE/DELETE
  • Create DB schemas through the dashboard
  • PostgreSQL, MySQL, MariaDB, SQL Server
  • SQL to English feature also available
  • Free during the beta period
  • All good... but there's no feature to import an existing schema. Having to set up a new DB through the dashboard just for this seems to be the flaw

See also

Favorite site

author Zer0 sitemap