A SQL guide to using SELECT, FROM and WHERE

SQL is a powerful tool for managing and manipulating data in a database.

In this blog post, we’ll explore the first three SQL commands and provide examples of how to use them effectively.

SQL Introduction: A guide to using SELECT, FROM and WHERE

1. USING SELECT

The SELECT command is one of the most basic and essential SQL commands. It is used to retrieve data from a database table.

Here are three examples of how to use the SELECT command:

Example 1: Retrieve all columns from a table

SELECT *
FROM customers;

This query retrieves all the columns from the customers table.

customer_id	first_name	last_name	email	phone	city
1	John	Doe	johndoe@xyz.com	555-123-4567	New York
2	Jane	Smith	janesmith@xyz.com	555-234-5678	Los Angeles
3	Bob	Johnson	bobjohnson@xyz.com	555-345-6789	Chicago
4	Mary	Brown	marybrown@xyz.com	555-456-7890	Houston

Example 2: Retrieve specific columns from a table

SELECT first_name, last_name, email
FROM customers;

This query retrieves only the first name, last name, and email columns from the customers table.

first_name	last_name	email
John	Doe	johndoe@xyz.com
Jane	Smith	janesmith@xyz.com
Bob	Johnson	bobjohnson@xyz.com
Mary	Brown	marybrown@xyz.com

Example 3: Retrieve data based on a condition

SELECT first_name, last_name, email
FROM customers;
WHERE first_name = 'John'

This query retrieves only the first name, last name, and email columns from the customers table where first name is John

first_name	last_name	email
John	Doe	johndoe@xyz.com

2. USING FROM

The FROM command is used to specify the table or tables from which data is to be retrieved.

Here are three examples of how to use the FROM command:

Example 1: Retrieve data from a single table

SELECT *
FROM customers;

This query retrieves all the columns from the customers table.

customer_id	first_name	last_name	email	phone	city
1	John	Doe	johndoe@xyz.com	555-123-4567	New York
2	Jane	Smith	janesmith@xyz.com	555-234-5678	Los Angeles
3	Bob	Johnson	bobjohnson@xyz.com	555-345-6789	Chicago
4	Mary	Brown	marybrown@xyz.com	555-456-7890	Houston

Example 2: Retrieve data from multiple tables using JOIN

SELECT customers.first_name, orders.order_date
FROM customers
JOIN orders 
ON customers.customer_id = orders.customer_id;

This query retrieves the first name of each customer and the date of each order by joining the customers and orders tables on the customer_id column. Only customers who have made an order will be returned in the output.

first_name	order_date
John	2022-01-02
Jane	2022-02-05
Bob	2022-02-22

Example 3: Retrieve data from a subquery

SELECT *
FROM (
    SELECT first_name, last_name, city
    FROM customers
    WHERE city = 'New York'
) AS ny_customers;

This query retrieves the first name, last name and city of customers who live in New York by using a subquery to filter the data and then using the subquery as a table in the outer query.

first_name	last_name	city
John	Doe	New York

3. USING WHERE

The WHERE command is used to filter rows based on a specified condition.

Here are three examples of how to use the WHERE command:

Example 1: Filter rows based on a single condition

SELECT *
FROM customers
WHERE city = 'Los Angeles';

This query retrieves all the columns from the customers table where the city is Los Angeles.

customer_id	first_name	last_name	email	phone	city
2	Jane	Smith	janesmith@xyz.com	555-234-5678	Los Angeles

Example 2: Filter rows based on multiple conditions

SELECT *
FROM orders
WHERE order_date >= '2022-01-01' 
AND total_amount > 1000;

This query retrieves all the columns from the orders table where the order date is on or after January 1, 2022, and the total amount is greater than 1000.

order_id	customer_id	order_date	total_amount
2	2	2022-02-05	399.99

Example 3: Filter rows based on a subquery

SELECT *
FROM orders
WHERE customer_id IN (
    SELECT customer_id
    FROM customers
    WHERE city = 'New York'
);

This query retrieves all the columns from the orders table where the customer_id is in the list of customer_ids returned by the subquery, which filters the customers table to only those who live in New York.

order_id	customer_id	order_date	total_amount
1	1	2022-01-02	120.50

Conclusion

In conclusion, the SELECT, FROM, and WHERE commands are the foundation of SQL programming.

You can write simple to complex SQL queries and manipulate data effectively by mastering these commands.