SQL SELECT
is probably the most widely used SQL statement. That’s why we have decided to investigate options that can be used in the query.
Besides, the article contains a bunch of use cases of the SELECT
statement with practical examples and lists three simple ways to retrieve data with the help of dbForge Studio for SQL Server.
Contents
- Introduction to SQL SELECT query
- The general syntax of the SELECT command
- SQL SELECT statement examples
- SELECT statements with dbForge Studio for SQL Server
- Conclusion
Introduction to SQL SELECT query
SQL SELECT
statement is used for fetching some data from a database. The statement is just a query that claims what data to be retrieved, where to search for it, and how to modify it before returning.
In most cases, the SELECT
query is run with FROM
. SELECT
states columns and FROM
indicates a table where these columns are located.
The general syntax of the SELECT command
The basic syntax of the query is as follows:
SELECT <column1>, <column2>, ...
FROM <table>;
Parameters:
<column1>
,<column2>
: the names of the columns you want to select data from<table>
: the name of the table where the specified columns are located
SELECT statement options
SQL SELECT
has different clauses to manage the data output. They are: FROM
, AS
, GROUP BY
, HAVING
, INTO
, ORDER BY
, *
(asterisk). Let’s see how we can use each clause within the SELECT
syntax.
1. FROM
is used to specify a table name where a necessary column with data is located.
Syntax:
SELECT <column>
<table>;
Parameters:
-
<column>
: the name of the column you want to select data from -
<table>
: the name of the table where a specified column is located
2. AS
is used to create a temporary name for the column headings. This method lets create more clear column headings. AS
is optional and can be present in the query for readability purposes.
Syntax:
SELECT <column> [ AS <new_column> ]
FROM <table> [ AS <new_table> ];
OR
SELECT <column> [ <new_column> ]
FROM <table> [ <new_table> ];
Parameters:
<column>
: the name of the column to be renamed<new_column>
: a new name that must be assigned to the column<table>
: the name of the table to be renamed<new_table>
: a new name that must be assigned to table
3. GROUP BY
is used to group results with similar data. There are some important things you should know about the clause:
GROUP BY
displays one record for each group.GROUP BY
is used with aggregate functionsCOUNT
,MAX
,MIN
,SUM
,AVG
etc.GROUP BY
follows theWHERE
clause, but precedes theORDER BY
clause in a query.
Syntax:
SELECT <column1>, SUM(<column2>)
FROM <table>
GROUP BY <grouping_column>;
Parameters:
<column1>
: the name of the column you want to select data from<column2>
: the name of a numeric column you want to retrieve a total sum from<table>
: the name of the table where a specified column is located<grouping_column>
: the name of the column by which the results will be grouped
4. HAVING
is used to define a search condition. The clause is used in combination with GROUP BY
.
Syntax:
SELECT <column1>, SUM(<column2>)
FROM <table>
GROUP BY <grouping_column>
HAVING <condition>;
Parameters:
-
<column1>
: the name of the column you want to select data from -
<column2>
: the name of a numeric column you want to retrieve a total sum from -
<table>
: the name of the table where a specified column is located -
<grouping_column>
: the name of the column by which the results will be grouped <condition>
: an additional search condition that will be applied for aggregated results
5. INTO
is used to create a new table and copy the retrieved results into it.
Syntax:
SELECT <column> INTO <new_table>
FROM <table>
WHERE <condition>;
Parameters:
-
<column>
: the name of the column you want to select and copy into a new table <new_table>
: the name of a new table to be created for further copying a specified column<table>
: the name of the table where a specified column is located<condition>
: a condition for filtering and fetching only the necessary records
6. ORDER BY
is used to filter retrieved results. The sorting can be organized in ascending (a default one) and descending order.
Syntax:
SELECT <column1>
FROM <table>
ORDER BY <column2>;
Parameters:
-
<column1>
: the name of the column you want to select data from -
<table>
: the name of the table where a specified column is located <column2>
: the name of the column to sort retrieved results
7. *
(asterisk) is used to get all columns and rows from a table.
Syntax:
SELECT *
FROM <table>;
Parameters:
<table>
: the name of the table you want to fetch all available columns and rows from
SQL SELECT statement examples
In this section, we are going to review various cases of using the SELECT
query on the examples based on two tables: Customer and Country.
The Customer table:
customer_id | name | country_id |
1 | Tom Crawford | 3 |
2 | Mia Chesterfield | 1 |
3 | Sally Colland | 4 |
4 | Peter Cromwell | 2 |
The Country table:
country_id | name |
1 | Canada |
2 | New Zealand |
3 | Thailand |
4 | Poland |
Select all columns from a table
To select all columns from a table, the SELECT
syntax is as follows:
SELECT *
FROM customer;
You will retrieve all available columns and rows from the Customer table.
How to exclude a column from the SELECT
Suppose that you want to select all the columns from the Customer table, except name
. In this case, a syntax example will look as follows:
SELECT customer_id, country_id
FROM customer;
Select data from specific column in SQL
To select data from a specific column in a table, see the query below:
SELECT name
FROM customer;
In the output, you will see the data available in the name column in the Customer table.
SELECT data from multiple tables with JOIN
It is also possible to select data from multiple tables with the help of JOIN
clauses. There are several different types of JOIN
clauses in SQL. For more information, please refer to Different types of JOINS in SQL Server.
In this section, we will provide the syntax example of SELECT
with INNER JOIN
to obtain data from multiple tables.
SELECT cs.name AS customer_name, cn.name AS country_name
FROM customer AS cs
INNER JOIN country AS cn
ON cs.country_id=cn.country_id;
The query will output data from the country_id columns from both the Customer and the Country tables.
SELECT statements with dbForge Studio for SQL Server
With dbForge Studio for SQL Server, you can retrieve data from a table in three simple ways. Let’s review each of them.
Way 1 – Using the Code completion feature:
1. In the SQL Editor, start typing SELECT. The drop-down menu will appear.
2. Double-click SELECT:
3. Fill the query and click Execute on the standard toolbar.
Way 2 – Using the Generate Script As option:
1. In Database Explorer, right-click the necessary table and navigate to Generate Script As > SELECT > To New SQL Window:
2. In the SQL document that opens, adjust the query and click Execute on the standard toolbar.
Way 3–using the Retrieve Data option:
This method allows getting all columns from a required table.
In Database Explorer, right-click the necessary table, then click Retrieve Data:
And voila, you will be able to see the data from all columns available in the table.
Conclusion
To summarize, we have explored the SELECT
statement with various options, supported each case with a specific example. Retrieving data becomes a simple process when you know how to use the query and in what order to apply its options. dbForge Studio for SQL Server can significantly simplify this task. Download its 30-day trial version and experience all the advantages.