📑 Table of Contents▼

A relational database management system uses SQL conditions or expressions in WHERE clauses and in HAVING clauses to SELECT subsets of data.

Types of condition

edit
  • Many conditions compare values for (for example) equality, inequality or similarity.
  • The EXISTS condition uses the SQL standard keyword EXISTS[1] to determine whether rows exist in a subquery result.[2]

Examples

edit

To SELECT one row of data from a table called tab with a primary key column (pk) set to 100 — use the condition pk = 100: 

SELECT * FROM tab WHERE pk = 100

To identify whether a table tab has rows of data with a duplicated column dk — use the condition having count(*) > 1: 

SELECT dk FROM tab GROUP BY dk HAVING count(*) > 1

Advanced conditional logic in SQL

edit

In addition to basic equality and inequality conditions, SQL allows for more complex conditional logic through constructs such as CASE, COALESCE, and NULLIF. The CASE expression, for example, enables SQL to perform conditional branching within queries, providing a mechanism to return different values based on evaluated conditions. This logic can be particularly useful for data transformation during retrieval, especially in SELECT statements. Meanwhile, COALESCE simplifies the process of handling NULL values by returning the first non-NULL value in a given list of expressions, which is especially useful in scenarios where data might be incomplete or missing. Furthermore, SQL's support for three-valued logic (True, False, Unknown) introduces nuances when handling NULL values in conditions, making it essential to carefully structure queries to account for the "Unknown" state that arises in certain comparisons with NULL values. Proper use of these advanced conditions enhances the flexibility and robustness of SQL queries, particularly in complex data retrieval and reporting environments.

References

edit
  1. ^ Fehily, Chris (2005). SQL: Visual Quickstart Guide (2 ed.). Peachpit Press. pp. 439–440, 480. ISBN 978-0-321-33417-6. SQL Keywords [...] The appendix lists the SQL:2003 standard's reserved and non-reserved keywords. [...] EXISTS [...]
  2. ^ Fehily, Chris (2005). SQL: Visual Quickstart Guide (2 ed.). Peachpit Press. p. 278. ISBN 978-0-321-33417-6. EXISTS and NOT EXISTS [...] look for the existence or nonexistence of rows in a subquery result.

📚 Artikel Terkait di Wikipedia

Join (SQL)

A join clause in the Structured Query Language (SQL) combines columns from one or more tables into a new table. The operation corresponds to a join operation

Condition

of exceptions in exception handling Condition (SQL), a filtering mechanism in relational database queries Condition variable, a synchronization primitive

Null (SQL)

In the SQL database query language, null or NULL is a special marker used to indicate that a data value does not exist in the database. Introduced by

List of SQL reserved words

reserved. SQL SQL syntax List of relational database management systems Page listing all reserved words in the SQL standard, from SQL-92 to SQL:2016: Standard

Merge (SQL)

system uses SQL MERGE (also called upsert) statements to INSERT new records or UPDATE or DELETE existing records depending on whether condition matches.

PostgreSQL

database management system (RDBMS) emphasizing extensibility and SQL compliance. PostgreSQL features transactions with atomicity, consistency, isolation,

MySQL

MySQL (/ˌmaɪˌɛsˌkjuːˈɛl/) is a free and open-source relational database management system (RDBMS). Its name is a combination of "My", the name of co-founder

SQL/PSM

Features P001-P008. SQL/PSM standardizes syntax and semantics for control flow, exception handling (called "condition handling" in SQL/PSM), local variables