SQL Database Queries: SELECT Statement Basics

The SQL SELECT statement is the most fundamental command for retrieving data from a database. This article explains the basic usage of the SELECT statement and how to specify search conditions.

Basic SELECT Syntax

Retrieving Data

The SELECT statement retrieves data by selecting columns from a specified table.

SELECT column_list FROM table_name;

• column_list: Specify column names separated by commas. Use * to retrieve all columns.
• table_name: Specify the name of the table to retrieve data from.

Examples:

SELECT column1, column2 FROM my_table; -- Retrieve column1 and column2 from my_table
SELECT * FROM my_table;             -- Retrieve all columns from my_table

Checking Table Structure

Use the DESCRIBE or DESC command to check a table’s structure (column names, data types, NULL constraints, etc.).

DESCRIBE table_name;
-- or
DESC table_name;

Example:

DESCRIBE my_table;

Eliminating Duplicates

The DISTINCT keyword eliminates duplicate values from selected columns, returning only unique values.

SELECT DISTINCT column_list FROM table_name;

Example:

SELECT DISTINCT category FROM products; -- Retrieve unique category names from products

Specifying Search Conditions

The WHERE clause retrieves only records that meet specific conditions.

SELECT column_list FROM table_name
WHERE condition;

Example:

SELECT * FROM users WHERE age > 30; -- Retrieve users older than 30 from the users table

Sorting Results

The ORDER BY clause sorts results by specified columns.

SELECT column_list FROM table_name
WHERE condition
ORDER BY column_name [ASC|DESC];

• ASC: Ascending order (default)
• DESC: Descending order

Examples:

SELECT name, age FROM users ORDER BY age DESC; -- Sort users by age in descending order
SELECT product_name, price FROM products ORDER BY price ASC, product_name ASC; -- Sort by price ascending, then by name ascending

Search Condition Operators

Key operators available in the WHERE clause:

• BETWEEN: Searches for values within a specified range (inclusive).

  SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';
  

• IN: Searches for values matching any item in a specified list.

  SELECT * FROM employees WHERE department_id IN (10, 20, 30);
  

• LIKE: Performs pattern matching on strings using wildcards.
  • %: Matches zero or more characters
  • _: Matches exactly one character

  SELECT * FROM products WHERE product_name LIKE 'Apple%'; -- Product names starting with 'Apple'
  SELECT * FROM customers WHERE phone_number LIKE '___-____-____'; -- Specific phone format
  

• IS NULL / IS NOT NULL: Searches for NULL values (absence of a value).

  SELECT * FROM users WHERE email IS NULL; -- Users without email addresses
  SELECT * FROM products WHERE description IS NOT NULL; -- Products with descriptions
  

Additional Notes

• Case sensitivity in string searches: Some database systems (RDBMS) may not distinguish between uppercase and lowercase in string searches by default (e.g., MySQL’s default setting). Check your RDBMS documentation for exact behavior and appropriate settings or functions when strict case sensitivity is needed.
• Logical operators: Use AND, OR, NOT to combine multiple conditions.
  • Precedence order: NOT, AND, OR.
  • Use parentheses () to explicitly change precedence.

  SELECT * FROM products WHERE category = 'Electronics' AND price > 10000;
  SELECT * FROM users WHERE (age < 18 OR age > 65) AND country = 'Japan';
  

Understanding these basic syntax elements and operators enables effective data retrieval from databases using SQL.