Introduction
In this chapter, we will learn how to SELECT SQL works and different options used with the SELECT statement.
SQL SELECT statement
SQL SELECT statement is used to select data from a database table or tables.
SQL SELECT statement is used to select records from an RDBMS( Relational Database Management System) data table. The SELECT statement can select all records, select a set of records based on a condition, filter records, sort records, group by records, and more.
Here are some of the use cases of the SQL SELECT statement.
- SQL Select *
- SQL Select Distinct
- SQL Select Where
- SQL Select Having
- SQL Select Order By
- SQL Select And, Or, Not
- SQL Select Insert Into
- Nested SQL Select
- SQL Select with Case
- SQL Select with If…Then
1. SQL SELECT *
SELECT * statement selects all rows from a database table.
SELECT all rows from a table, EmployeeDetail.
The output looks like the following
NOTE
SELECT * is not recommended unless you need all the columns data. Use SELECT Column1, Column2 fromTable_Name format for better performance.
SELECT Column names
The following SELECT statement uses column names to select data from a table.
SQL Example
- Select EmpName, EmpAddress from EmployeeDetail
The output looks like the following
2. SQL SELECT DISTINCT
The SELECT DISTINCT statement is used to return only DISTINCT(different) values.
Inside a table, a column may often contain many duplicate values and sometimes you only want to list the different (distinct) values.
- SELECT DISTINCT column-name
- FROM table-name
The output looks like the following
3. SELECT FROM WHERE
- SELECT WHERE statement is used to filter records.
- SELECT WHERE statement is used to extract only those records that fulfill a specified condition.
- SELECT WHERE statement is not used in the SELECT statement, it is used in UPDATE, DELETE statement, etc.
Example : = > < >= <= <>
This is an example of equals (=) statement
This is an example of greater than (>) statement
This is an example of less than (<) statement
This is an example of a greater than or equal (>=) statement
This is an example of a less than or equal (<=) statement
Less than greater than not equal (< >).
Note: in some versions of SQL this operator may be written as !=
This is an example of < > statement
BETWEEN
The BETWEEN statement is used BETWEEN a certain range for the SQL
The output looks like the following
LIKE
The LIKE operator is used in a WHERE clause statement to search for a specified pattern in a column in the SQL.
This is used in two wildcards, it is often used in conjunction with the LIKE operator:
- % - The percent sign represents zero, one, or multiple characters
- _ - The underscore represents a single character
The output looks like the following
IN
The IN operator is used to specify multiple values in a WHERE clause.
The IN operator is a shorthand for multiple OR conditions in SQL.
The output looks like the following
- SELECT * FROM EmployeeDetail
- SELECT GROUP BY
4. SELECT HAVING
HAVING can be used only in the SELECT statement. HAVING is typically used with the GROUP BY clause. When GROUP BY is not used, there is an implicit single, aggregated group.
Syntax:
- SELECT
- select_list
- FROM
- table_name
- GROUP BY
- group_list
- HAVING
- conditions;
The output looks like the following
5. SELECT ORDER BY
The SELECT ORDER BY statement is the result set of a query by the specified column list and, optionally, limits the rows returned to a specified range. The order in which rows are returned in a result set is not guaranteed unless an ORDER BY clause is specified.
Syntax :
- SELECT column1, column2,
- FROM table_name
- ORDER BY column1, column2 ASC|DESC;
The output looks like the following
6. SQL SELECT AND, OR, NOT
SELECT AND statement:
- SELECT AND statement require two conditions to be true in a WHERE statement.
- SELECT WHERE statement with NOT negates the specified condition
- SELECT AND statement: A WHERE statement with AND requires that two conditions are true
Syntax
- SELECT column1, column2
- FROM table_name
- WHERE condition1 AND condition2 AND condition3;
The output looks like the following
SELECT OR statement
SELECT OR statement requires that one of two conditions is true in a where statement in the SQL.
Syntax
- SELECT column1, column2,
- FROM table_name
- WHERE condition1 OR condition2 OR condition3;
The output looks like the following
SELECT NOT statement
a WHERE statement with NOT negates the specified condition
The NOT operator displays a record if the condition(s) is NOT TRUE.
- SELECT column1, column2,
- FROM table_name
- WHERE NOT condition;
The output looks like the following
7. SQL SELECT INSERT INTO
The SQL SELECT INSERT INTO Select statement copies data from one table and insert data into another table.
- INSERT INTO table2
- SELECT * FROM table1
- WHERE condition;
The output looks like the following
8. Nested SQL SELECT statement
a subquery can be used anywhere where an expression is allowed in the Nested SQL SELECT statement
Syntax
- SELECT column_name [, column_name ]
- FROM table1 [, table2 ]
- WHERE column_name OPERATOR
- (SELECT column_name [, column_name ]
- FROM table1 [, table2 ]
- [WHERE])
The output looks like the following
9. SQL SELECT with CASE
The SQL SELECT with the CASE statement goes through conditions and returns a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.
If there is no ELSE part and no conditions are true, it returns NULL.
Syntax
- CASE
- WHEN condition1 THEN result1
- WHEN condition2 THEN result2
- WHEN conditionN THEN resultN
- ELSE result
- END;
The output looks like the following
10. SQL SELECT with IF…THAN
The SQL SELECT with IF and THAN statement goes through conditions and returns a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.
If there is no ELSE part and no conditions are true, it returns NULL.
Syntax
- IF <Condition>
- {Statement | Block_of_statement}
- [ ELSE
- {Statement | Block_of_statement}]
The SQL * is harmful as of the following points
1. Unnecessary I/O
2. Increased network traffic
3. More application memory
4. Depends on the column order
5. Fragile views
6. Conflict in a join query
7. Risky while copying data.
Summary
In the next chapter, you will learn how to use SQL SELECT -HAVING BY.