One of the core operations in SQL is fetching data from a database. SQL provides several powerful clauses and keywords to retrieve and filter data efficiently. In this section, we’ll explore basic data retrieval using the SELECT statement, along with the SELECT DISTINCT and WHERE clauses.
Table of Contents
1. The SELECT Statement
The SELECT statement is fundamental for retrieving data from a database. It enables you to define the specific columns you want to extract from a given table, making it one of the most frequently used commands in SQL.
Syntax:
SELECT column1, column2, ...
FROM table_name;- column1, column2: Specify the columns you want to retrieve.
- table_name: The name of the table where the data is stored.
Example:
Suppose we have a table called Employees:
| EmployeeID | FirstName | LastName | Department | Salary |
| 1 | John | Doe | HR | 50000 |
| 2 | Jane | Smith | IT | 60000 |
| 3 | Robert | Johnson | Finance | 70000 |
| 4 | Emily | Davis | IT | 60000 |
To retrieve the FirstName and Salary of all employees, we use:
SELECT FirstName, Salary
FROM Employees;Result:
| FirstName | Salary |
| John | 50000 |
| Jane | 60000 |
| Robert | 70000 |
| Emily | 60000 |
2. The SELECT DISTINCT Statement
When retrieving data, you may encounter duplicate values in one or more columns. SELECT DISTINCT is used to return only unique (distinct) values, filtering out duplicates.
Syntax:
SELECT DISTINCT column1, column2, ...
FROM table_name;Result:
| Department |
| HR |
| IT |
| Finance |
Even though there are two employees in the IT department, SELECT DISTINCT returns only one row for the IT department.
3. The WHERE Clause
The WHERE clause is used to filter records and retrieve only those that meet a specific condition. You can use comparison operators like =, >, <, >=, <=, and logical operators like AND, OR, and NOT to construct conditions.
Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE condition;condition: A logical expression that must be true for the row to be included in the result set.
Example 1 (Single Condition):
To retrieve employees from the Employees table who work in the IT department:
SELECT FirstName, LastName
FROM Employees
WHERE Department = 'IT';Result:
| FirstName | LastName |
| Jane | Smith |
| Emily | Davis |
Example 2 (Multiple Conditions):
To retrieve employees who work in the IT department and have a salary greater than 55000:
SELECT FirstName, LastName, Salary
FROM Employees
WHERE Department = 'IT' AND Salary > 55000;Result:
| FirstName | LastName | Salary |
| Jane | Smith | 60000 |
| Emily | Davis | 60000 |
Example 3 (Using OR):
To retrieve employees who either work in the Finance department or have a salary greater than 60000:
SELECT FirstName, LastName, Department, Salary
FROM Employees
WHERE Department = 'Finance' OR Salary > 60000;Result:
| FirstName | LastName | Department | Salary |
| Robert | Johnson | Finance | 70000 |
Using Comparison Operators with WHERE
- =: Equal to.
- <> or !=: Not equal to.
- <: Less than.
- <=: Less than or equal to.
- >: Greater than.
- >=: Greater than or equal to.
Example:
To retrieve employees with a salary less than or equal to 60000:
SELECT FirstName, LastName, Salary
FROM Employees
WHERE Salary <= 60000;Result:
| FirstName | LastName | Salary |
| John | Doe | 50000 |
| Jane | Smith | 60000 |
| Emily | Davis | 60000 |
Conclusion
Basic data retrieval in SQL is straightforward with the SELECT statement, which helps you fetch data from one or more columns. To avoid duplicates, use SELECT DISTINCT, and when you need to filter your results, the WHERE clause is essential. By understanding these basics, you can start building more complex queries to manipulate and retrieve the data you need efficiently.
Most Commented