When working with databases, one common function is to create a query whereby a database programmer or administrator needs to select some data from from a table within a database. In SQL (Structure Query Language), this is accomplished by using the SQL SELECT statement. And that is the topic of today’s database programming tutorial.
Read: Best Certifications for Database Administrators
The SQL SELECT Statement
In SQL, programmers can retrieve data from a table using the SQL SELECT statement. The syntax for the SELECT statement can be seen in the following example code:
/* Syntax for SQL SELECT Statement */ SELECT columnA FROM table_example;
In this above SQL example, we identify that we want to SELECT all of the data that is stored in columnA, which resides in the table named table_example. If we had a table that had columns representing first names stored in columnA, then the above query would return every name in that column.
How to Return Multiple Columns in a Table with SQL SELECT
But what if we wanted to return more than one column in our table? Suppose we have two columns in table_example: columnA stores first names and columnB stores last names. If we wanted to retrieve both of these columns, we could simply modify our previous code example to reflect the following:
/* Syntax for SQL SELECT Statement to retrieve multiple columns*/ SELECT columnA, columnB FROM table_example;
When we run the above query, both columnA and columnB get returned from the table_example table.
How to Return Every Column in a Table with SQL SELECT
If you have a table in a database with multiple columns and you wish to retrieve all of the data from all of the columns in a particular table, you can do so by using a wildcard operator, represented by an asterisk *. Here is how you would return every column in a table using the SQL SELECT statement:
/* Syntax for SQL SELECT statement to retrieve every column in a table */ SELECT * FROM table_example;
SQL SELECT Parameters and Arguments
The SQL SELECT statement has sever optional parameters and arguments that database developers can use to enhance their queries. These include:
- expressions: In the SELECT statement, expressions are the columns or calculations that you want to retrieve from a table or database.
- tables: These are the tables that you want to retrieve information from. A minimal of one table must be chosen following the FROM clause.
- WHERE: The WHERE condition is used to dictate what conditions have to be met in order for a record(s) to be selected and returned. If this parameter is left blank or empty, every record will be chosen.
- ORDER BY: This argument is also optional and is used to dictate the order that records are to be returned. The column you want to order by must follow the ORDER BY argument. Then, you must include the order in which you want results sorted, using the (also optional) ASC or DESC parameters. Note that you can also sort by multiple columns; you simply separate the columns you want to sort by with a comma (,).
Here is a code example showing how to use SQL WHERE in a SELECT statement:
SELECT columnA FROM table_example WHERE columnA = “Ronnie”
In the above example SQL code, we are telling the database to search columnA from the table named table_example and return results only for cells that contain the text “Ronnie”. Since we did not specify the order in which results are returned, they will be returned by ascending order by default.
Further note that the query will only return results with the word “Ronnie” in it. For example, if there is a cell in columnA that contains the word “RonnieBonnie” and one that contains “Ronnie”, only the “Ronnie” result will be returned.
Read: Best Online Courses to Learn SQL
The SQL LIKE Clause
If we wanted to include any result that contained the string “Ronnie”, then we would use the SQL LIKE statement, alongside one of its two wildcard characters:
- – used to match a single character
- % used to match any number of characters before or after the string
Observe the following code example, which shows how to use the LIKE clause, WHERE clause, FROM, and the SQL SELECT statement to return any results from columnA that contain any text with “Ronnie”, regardless of what text comes before or after the word “Ronnie”:
SELECT columnA FROM table_example WHERE columnA = “%Ronnie%”
Note the % wildcard in the front and back of our string “Ronnie”. The % symbol in front of the word means that any number of characters can appear before the word “Ronnie” and the % wildcard after the text means that any characters can appear after the text “Ronnie”. If we removed the first %, then no characters would be allowed prior to the text “Ronnie”, but any text could appear after the text “Ronnie”.
The minus – wildcard works the same way, only it indicates any single character may appear. For instance, in this scenario:
SELECT columnA FROM table_example WHERE columnA = “-Ronnie-”
the following results would be valid:
“Ronnie” “Bronnie” “Bronnies” “Ronnies”
These results, however, would not be valid, because more than one character exists in front of and/or behind the text “Ronnie”:
“Scronnies” “Scronnie” “Ronniest”
Finally, to sort our results in ascending or descending order, we simply use the ASC or DESC parameters of the ORDER BY clause:
SELECT columnA FROM table_example WHERE columnA = “%Ronnie%” ORDER BY columnA ASC;
If we were selecting data from multiple columns – and sorting by multiple columns – then we could use an SQL query similar to the following:
SELECT columnA, columnB FROM table_example WHERE columnA = “%Ronnie%” ORDER BY columnA ASC, columnB DESC;
In this example, any result containing “Ronnie” in columnA will be returned, along with any corresponding data from columnB. Then that data will be sorted by the values in columnA in ascending order first, then columnB in descending order.
Read: Project Management Software for Database Developers
The post How to Use the SQL SELECT Statement appeared first on Database Journal.
