SqlDatabaseBeginner

SELECT Statement

Retrieves data from one or more database tables

Review the syntaxStudy the examplesOpen the coding app
SELECT column1, column2 FROM table_name WHERE condition;
Open Coding Reference

This static page keeps the syntax and examples indexed for search, while the coding app handles interactive exploration and saved references.

What it does

Overview

Retrieves data from one or more database tables

The SELECT statement is the primary operation for Data Query Language (DQL) in SQL, responsible for retrieving data from database objects like tables and views. To optimize performance, it is critical to avoid the 'SELECT *' wildcard in production, as it increases I/O overhead and can lead to application failure if the underlying schema changes. Queries should be designed to be SARGable (Search Argumentable), meaning the WHERE clause should utilize indexed columns without wrapping them in functions to ensure the optimizer can perform index seeks instead of full table scans. A significant edge case involves the handling of NULL values; since NULL represents an 'unknown' state, standard equality operators (=, !=) will not work, requiring the use of 'IS NULL' or 'IS NOT NULL'. Furthermore, result sets are non-deterministic by default; without an ORDER BY clause, the database does not guarantee any specific record sequence. When performing date comparisons, using ISO 8601 strings ('YYYY-MM-DD') ensures consistent behavior across different regional database configurations.

Quick reference

Syntax

SELECT column1, column2 FROM table_name WHERE condition;

Inputs

Parameters

columnsColumn names to retrieve, or * for all columns
table_nameThe table to query from
WHERE condition (optional)Optional filter condition

See it in practice

Examples

1

Filtering and Column Selection

SELECT product_name, unit_price FROM products WHERE unit_price > 100 AND discontinued = 0;
Output:
product_name | unit_price Laptop | 1200.00 Smartphone | 800.00

Retrieves specific columns for active products exceeding a price threshold, utilizing named columns for better performance.

2

Aggregating Data with HAVING

SELECT category_id, COUNT(*) AS product_count FROM products GROUP BY category_id HAVING COUNT(*) > 5;
Output:
category_id | product_count 1 | 12 3 | 8

Groups records by category and filters the resulting groups using the HAVING clause.

3

Date Range Query with Ordering

SELECT order_id, customer_id FROM orders WHERE order_date >= '2023-01-01' AND order_date <= '2023-12-31' ORDER BY order_date DESC;
Output:
order_id | customer_id 10501 | VINET 10502 | HANAR

Retrieves orders from a specific year using ISO date strings and ensures the most recent orders appear first.

Debug faster

Common Errors

1

LogicError

Cause: Attempting to filter for NULL values using the equality operator (=).

Fix: Use the 'IS NULL' operator instead of '='.

SELECT * FROM users WHERE deleted_at = NULL;
2

SyntaxError

Cause: Incorrect placement of the WHERE clause after a GROUP BY clause.

Fix: Place the WHERE clause before GROUP BY; use HAVING for post-aggregation filtering.

SELECT dept_id FROM staff GROUP BY dept_id WHERE salary > 50000;

Runtime support

Compatibility

All SQL databasesSQL-92 (Standard)

Core SQL feature, universally supported

Source: MDN Web Docs

Common questions

Frequently Asked Questions

Retrieves data from one or more database tables

columns: Column names to retrieve, or * for all columns ame: The table to query from WHERE condition: Optional filter condition

LogicError: Use the 'IS NULL' operator instead of '='. SyntaxError: Place the WHERE clause before GROUP BY; use HAVING for post-aggregation filtering.