MySQL SELECT

Retrieving data from your database

🔍 What is SELECT?

SELECT is the most important SQL command for retrieving data from databases. It allows you to query tables, filter results, and display specific information. Every data retrieval operation starts with SELECT. 


-- Basic SELECT query
SELECT * FROM employees;

SELECT Statement Components

📋

SELECT Clause

Specifies which columns to retrieve from the table. Use * to select all columns or list specific column names separated by commas. This determines what data appears in your query results.

📊

FROM Clause

Indicates which table to query. Every SELECT statement needs a FROM clause to specify the data source. You can query single tables or join multiple tables to combine related information together.

🎯

Column Selection

Choose specific columns instead of using *. This improves performance by retrieving only needed data. You can also use aliases to rename columns in results, making output more readable and meaningful.

🔢

DISTINCT

Removes duplicate rows from results. When you need unique values, add DISTINCT after SELECT. This is useful for finding all unique categories, cities, or any other values where duplicates should be eliminated.

🔹 Basic SELECT Syntax

The simplest SELECT retrieves all data from a table using the asterisk (*) wildcard. This shows every column and row. While convenient for exploration, it's better to specify columns in production queries for efficiency. 

-- Select all columns and rows
SELECT * FROM products;

-- Select specific columns
SELECT product_name, price FROM products;

-- Select with column alias
SELECT product_name AS name, price AS cost FROM products;

Output:

name cost
Laptop 899.99
Mouse 25.50

🔹 SELECT DISTINCT

DISTINCT eliminates duplicate values from your results. When a column contains repeated values, DISTINCT returns only unique entries. This is perfect for finding all different categories, locations, or status values in your data. 

-- Sample data
CREATE TABLE orders (
    order_id INT,
    customer_name VARCHAR(50),
    city VARCHAR(50)
);

INSERT INTO orders VALUES
(1, 'John', 'New York'),
(2, 'Alice', 'London'),
(3, 'Bob', 'New York'),
(4, 'Carol', 'Paris');

-- Get unique cities
SELECT DISTINCT city FROM orders;

Output:

city
New York
London
Paris

🔹 Sorting Results with ORDER BY

ORDER BY sorts query results by one or more columns. Use ASC for ascending (default) or DESC for descending order. Sorting makes data easier to read and analyze, especially when dealing with large result sets. 

-- Sort by price ascending
SELECT product_name, price
FROM products
ORDER BY price ASC;

-- Sort by price descending
SELECT product_name, price
FROM products
ORDER BY price DESC;

-- Sort by multiple columns
SELECT product_name, category, price
FROM products
ORDER BY category ASC, price DESC;

Output (DESC):

product_name price
Laptop 899.99
Mouse 25.50

🔹 Limiting Results with LIMIT

LIMIT restricts the number of rows returned by your query. This is useful for pagination, getting top results, or testing queries on large tables. You can also specify an offset to skip rows. 

-- Get first 5 products
SELECT * FROM products
LIMIT 5;

-- Get 5 products starting from row 10 (pagination)
SELECT * FROM products
LIMIT 5 OFFSET 10;

-- Alternative syntax
SELECT * FROM products
LIMIT 10, 5;  -- Skip 10, return 5

Output: 

5 rows returned

🔹 Calculated Columns

You can perform calculations in SELECT statements. Use arithmetic operators to create new columns based on existing data. This is useful for computing totals, percentages, or any derived values without storing them permanently. 

-- Calculate discounted price
SELECT 
    product_name,
    price,
    price * 0.9 AS discounted_price,
    price - (price * 0.9) AS savings
FROM products;

-- Calculate total with tax
SELECT 
    product_name,
    price,
    price * 1.08 AS price_with_tax
FROM products;

Output:

product_name price discounted_price
Laptop 899.99 809.99

🧠 Test Your Knowledge

What does SELECT * mean?