Intro to SQL: How to Use SQL SELECT and SELECT DISTINCT

Structured Query Language (SQL) is the standard programming language used to communicate with relational databases. Because data usage in business is growing at a staggering rate, the demand for people who understand SQL, relational databases, and data management is rising.

The American National Standards Institute establishes SQL as a standard for relational database management, so programmers and administrators can learn just one single language that works with only minor adjustments across a wide variety of database platforms, applications, and products. If you’re looking to build a career in programming or database management or if you’d like to better understand the workings of your website or company databases, SQL is an important language to learn.

SQL is a language with a syntax all its own, consisting of statements, clauses, and other pieces of code such as operators that establish parameters for limiting the query. An SQL statement is a complete piece of code that goes to a database management system, or DBMS, in order to perform a variety of functions on data contained in the database. You can request specific information from the database by writing a query, which is a statement that returns or delivers the information you want from the tables within the database. The SELECT statement is the most common operation in SQL, as it specifies which data to return from the database.

The SELECT Statement and Its Significance

SELECT statements are frequently called Data Query Language (DQL) in SQL. In relational databases, you store data in table form — under columns and rows defined by the user — and those tables populate with the INSERT statement. The SELECT command operates with the FROM clause to retrieve or extract information from database tables and present it in an organized and readable way.

The SELECT keyword in the query states which rows and columns you want to display as the result set of a query. Using the SELECT statement, you can specify the values that match the rows in the table that you want the query to return.

In its simplest form, the SELECT statement must contain the following elements: a SELECT clause that specifies the columns containing the values matching the query and a FROM clause that specifies the TABLE containing the columns listed in the SELECT clause.

The SELECT statement can also have many optional clauses that refine the query and return precise results. Commonly used clauses include:

• WHERE. The SQL WHERE command specifies the rows to retrieve.
• GROUP BY. The SQL GROUP BY command groups rows that share a property so that an aggregate function applies to each group.
• HAVING. The SQL HAVING command selects specific features among the groups defined by the GROUP BY clause.
• AS. The SQL AS command provides an alias for temporarily renaming tables or columns.
• ORDER. The SQL ORDER command orders the query results by a given key, such as the primary key. The ORDER BY clause can be very useful for organizing results.

The basic syntax for a SELECT statement looks like this:

SELECT [column 1, column 2, …] FROM source_table;

To display all the columns from a table in the result set, use the symbol “*” after SELECT. Likewise, you can set other flags such as the option to return only unique rows (with the ONLY UNIQUE) modifier.

Combining SQL SELECT and INSERT Statements

An INSERT statement containing a nested SELECT statement allows you to quickly populate a table with one or more rows from the result set of the SELECT command. You can insert multiple records in this way, like this:

INSERT INTO table (column1, column2, … ) SELECT expression1, expression2, … FROM source_tables WHERE conditions;

The syntax of this statement contains several parameters:

• “table” is the table where you should insert the result set of records.
• “column1, column2” names the columns in the tables where you place the values.
• “expression1, expression2” are the values to assign to the columns in the table.

That means “column1” will have the value of “expression1,” “column2” will have the value of “expression2,” and so on. The ellipsis, or triple dots, after each of these elements indicates that the operation will apply to other columns and expressions in the series:

• “source tables” indicate the source table when inserting data from another table.
• “conditions” are the conditions that must be met in order to insert the records correctly.

When performing insertions, you can also use common keywords such as auto increment (which creates an auto incrementing key).

The SQL SELECT INTO Statement

The SELECT INTO statement selects data from one table and inserts it into a new table. You can choose one or more columns, or all columns, to copy into the new table. If you want to copy all columns, use the “*” after the SELECT statement. The syntax for this statement looks like this:

SELECT * INTO newtable [IN externaldb] FROM table1

The syntax in this statement includes four parameters. 

• SELECT* INTO selects all the columns in the first table.

• “newtable” names the destination for the selected columns.
• “externaldb” names the source database.
• “table1” is the source table in the external database.

Here’s an example of an SQL SELECT INTO statement that creates a copy of an entire table of Vendors in the SQL server:

SELECT * INTO Vendorcopy2014 FROM Vendors;

But if we want to copy only specific columns into the new table, we’ll enter:

SELECT [column 1, column 3, ….] INTO newtable FROM table1;

This statement will generate a completely new table with the column names and types defined in the SELECT operator. For example, suppose we want to copy only the columns “Vendor Name” and “Country Name” into the new table. That statement looks like this:

SELECT VendorName, CountryName INTO Vendorcopy2014 FROM Vendors;

The SQL SELECT DISTINCT Statement

The DISTINCT clause is used in a SELECT statement to remove duplicates from the result set of the query. Here is the syntax for this statement:

SELECT DISTINCT expressions FROM tables WHERE conditions;

This statement includes these parameters:

• “expressions” are the columns or calculations you want to retrieve
• “tables” are the tables you want to retrieve records from — you need to list at least one table in the FROM clause.
• “conditions” names the conditions that must be met in order to select the records

If you don’t want to remove duplicate data, or if you’re sure that there will be no duplicates in the result set, then you can use ALL instead of DISTINCT. But ALL is the default keyword in SQL statements, so we usually omit it from query statements.

Conclusion

This tutorial provides an overview of the SQL SELECT statement and related clauses, but there’s so much more to explore. Take a look at our blog that highlights the most useful SQL commands to learn how to write basic SQL queries and other code statements. As you develop the knowledge and skills to become a database administrator and development, you’ll need to begin writing customized SQL code in order to meet the requirements of users as efficiently as possible.

If you want another in-depth look at another one of SQL’s most important operators, let’s progress to the INSERT INTO command.