The Select command in SQL is one of the most powerful and heavily used commands. This is I guess the first command anyone learns in SQL even before CREATE which is used to create a table in SQL. SELECT is used in SQL to fetch records from database tables and you can do a lot many things using Select. For example, you can select all records, you can select few records based on the condition specified in the WHERE clause, select all columns using the wild card (*) or only selecting a few columns by explicitly declaring them in a query.
In this SELECT SQL command tutorial, we will see some examples of select command or Select Statement and will write SQL queries to demonstrate the result.
We will use the following table and data for our SQL query examples, one table represent Stocks listed in various market and another table contains Details of market e.g. Country.
MySQL is my favorite RDBMS and great for learning purposes you can download MySQL and start working on it.
We will use the following table and data for our SQL query examples, one table represent Stocks listed in various market and another table contains Details of market e.g. Country.
MySQL is my favorite RDBMS and great for learning purposes you can download MySQL and start working on it.
My suggestion is to use a command-line interface for writing queries instead of using GUI e.g. SQL Developer or MySQL query tool.
Command-line is best for learning and the real fun of writing SQL queries is only on the command prompt.
mysql> select * from STOCK;
+---------+-------------------------+--------------------+
| RIC | COMPANY | LISTED_ON_EXCHANGE |
+---------+-------------------------+--------------------+
| 6758.T | Sony | T |
| GOOG.O | Google Inc | O |
| GS.N | Goldman Sachs Group Inc | N |
| INFY.BO | Infosys | BO |
| VOD.L | Vodafone Group PLC | L |
+---------+-------------------------+--------------------+
5 rows in set (0.00 sec)
mysql> select * from MARKET;
+------+-------------------------+---------------+
| RIC | NAME | COUNTRY |
+------+-------------------------+---------------+
| T | Tokyo Stock Exchange | Japan |
| O | NASDAQ | United States |
| N | New York Stock Exchange | United States |
| BO | Bombay Stock Exchange | India |
+------+-------------------------+---------------+
4 rows in set (0.00 sec)17 SQL SELECT Query Examples for Beginners
Here are some of my favorite select clause examples that explore different ways one can use the select command for reporting purposes and display results.
1) Finding how many rows in tables
You can use count(*) method to find how many rows are their in the table.
mysql> select count(*) from STOCK;
+----------+
| count(*) |
+----------+
| 5 |
+----------+
2) Finding all records from tables; we are using wildcard start * for getting all columns.
You can use the wild card * to show all the columns of a table as shown below, this is also a good way to find out name of all columns in a given table or number of columns.
mysql> select * from STOCK;
+---------+-------------------------+--------------------+
| RIC | COMPANY | LISTED_ON_EXCHANGE |
+---------+-------------------------+--------------------+
| 6758.T | Sony | T |
| GOOG.O | Google Inc | O |
| GS.N | Goldman Sachs Group Inc | N |
| INFY.BO | InfoSys | BO |
| VOD.L | Vodafone Group PLC | L |
+---------+-------------------------+--------------------+
5 rows in set (0.00 sec)
3. Selecting a few records based on some condition from tables in SQL
You can use the WHERE Clause to filter rows based upon conditions
mysql> select * from STOCK where RIC='GOOG.O';
+--------+------------+--------------------+
| RIC | COMPANY | LISTED_ON_EXCHANGE |
+--------+------------+--------------------+
| GOOG.O | Google Inc | O |
+--------+------------+--------------------+
4. How to select a few columns instead of all columns?
Instead of using start wild-card just give the name of interesting columns to the SELECT clause.
mysql> select COMPANY from STOCK where RIC='GOOG.O';
+------------+
| COMPANY |
+------------+
| Google Inc |
+------------+
5. Select distinct (unique) records from Columns
The distinct keyword is used to show only unique records it will not show any duplicate values.
mysql> select distinct LISTED_ON_EXCHANGE from Stock;
+--------------------+
| LISTED_ON_EXCHANGE |
+--------------------+
| T |
| O |
| N |
| BO |
| L |
+--------------------+
6. Selecting value with condition based on less than, greater than (>, <, >=, <=) etc.
You can use conditional operator like greater than, less than, greater than equal to and less than equal to in WHERE clause for filtering
mysql> select * from Stock where RIC > 'I';
+---------+--------------------+--------------------+
| RIC | COMPANY | LISTED_ON_EXCHANGE |
+---------+--------------------+--------------------+
| INFY.BO | InfoSys | BO |
| VOD.L | Vodafone Group PLC | L |
+---------+--------------------+--------------------+
7. Combining condition using logical operator AND & OR
AND and OR Can be effectively used to combine two conditions on the WHERE clause and gives you a lot of flexibility to write SQL queries.
mysql> select * from Stock where RIC <'I' AND RIC > 'G';
+--------+-------------------------+--------------------+
| RIC | COMPANY | LISTED_ON_EXCHANGE |
+--------+-------------------------+--------------------+
| GOOG.O | Google Inc | O |
| GS.N | Goldman Sachs Group Inc | N |
+--------+-------------------------+--------------------+
You can put any number of AND, OR conditions on WHERE Clause, sometimes things become quite easy when you combine AND, OR in SQL.
8. How to find records which are not null using keyword NULL and IS NULL
NULL is very tricky in SQL; NULL means anything which doesn't have value. NULL is not "null" which will be treated as text. To demonstrate this we will insert a Stock which is not listed on any Market yet.
mysql> select * from STOCK;
+---------+-------------------------+--------------------+
| RIC | COMPANY | LISTED_ON_EXCHANGE |
+---------+-------------------------+--------------------+
| 6758.T | Sony | T |
| GOOG.O | Google Inc | O |
| GS.N | Goldman Sachs Group Inc | N |
| INDIGO | INDIGO Airlines | NULL |
| INFY.BO | InfoSys | BO |
| VOD.L | Vodafone Group PLC | L |
+---------+-------------------------+--------------------+
6 rows in set (0.00 sec)
See there is only one row who has LISTED_ON_EXCHANGE null, we will now see count using NULL and IS NULL which will verify this result.
mysql> select count(*) from STOCK where LISTED_ON_EXCHANGE IS NULL;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from STOCK where LISTED_ON_EXCHANGE IS NOT NULL;
+----------+
| count(*) |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from STOCK;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.00 sec)
9. SELECT Statement using BETWEEN and NOT BETWEEN
As the name suggests BETWEEN is used to get data between ranges. You can use BETWEEN even with DATE columns to check if a given date is between two dates or not.
mysql> select * from Stock where RIC BETWEEN 'G' AND 'I';
+--------+-------------------------+--------------------+
| RIC | COMPANY | LISTED_ON_EXCHANGE |
+--------+-------------------------+--------------------+
| GOOG.O | Google Inc | O |
| GS.N | Goldman Sachs Group Inc | N |
+--------+-------------------------+--------------------+
10. Pattern matching in SQL queries using LIKE and NOT LIKE
LIKE is a pattern matching operator and used to find records that are not an exact match but a probable match.
mysql> select * from Stock where RIC LIKE 'V%';
+-------+--------------------+--------------------+
| RIC | COMPANY | LISTED_ON_EXCHANGE |
+-------+--------------------+--------------------+
| VOD.L | Vodafone Group PLC | L |
+-------+--------------------+--------------------+
NOT LIKE is the opposite of LIKE and display records which are not probable match.
mysql> select * from Stock where RIC NOT LIKE 'V%';
+---------+-------------------------+--------------------+
| RIC | COMPANY | LISTED_ON_EXCHANGE |
+---------+-------------------------+--------------------+
| 6758.T | Sony | T |
| GOOG.O | Google Inc | O |
| GS.N | Goldman Sachs Group Inc | N |
| INDIGO | INDIGO Airlines | NULL |
| INFY.BO | InfoSys | BO |
+---------+-------------------------+--------------------+
11. IN and NOT IN
IN is another useful SQL operator we can use alongside SELECT. it provides a set of values which can be used in WHERE clause.
mysql> select * from Stock where RIC in ('GS.N' , 'INFY.BO');
+---------+-------------------------+--------------------+
| RIC | COMPANY | LISTED_ON_EXCHANGE |
+---------+-------------------------+--------------------+
| GS.N | Goldman Sachs Group Inc | N |
| INFY.BO | InfoSys | BO |
+---------+-------------------------+--------------------+
12. Sorting ResultSet in SQL using ORDER BY, ASC, DESC
Order by is used to sort records in the result set returned by the SELECT clause. By default, its lists in Ascending order but we can use either ascending or descending using specifier ASC and DESC.
mysql> select * from Stock order by COMPANY;
+---------+-------------------------+--------------------+
| RIC | COMPANY | LISTED_ON_EXCHANGE |
+---------+-------------------------+--------------------+
| GS.N | Goldman Sachs Group Inc | N |
| GOOG.O | Google Inc | O |
| INDIGO | INDIGO Airlines | NULL |
| INFY.BO | InfoSys | BO |
| 6758.T | Sony | T |
| VOD.L | Vodafone Group PLC | L |
+---------+-------------------------+--------------------+
13. Selecting data from multiple tables by using JOIN in SQL
Join in SQL is a powerful concept which allows you to select data from multiple tables. You can generate a report where data is accumulated from different tables based on conditions specified in Join statement.
Suppose you need to “display a list of Records and Name of Market where they are listed”. Here the name of Stock in the STOCK table while the name of exchange in the MARKET table. We need to join both of them to display this report.
mysql> select s.RIC, m.NAME from Stock s, Market m where s.LISTED_ON_EXCHANGE=m.RIC;
+---------+-------------------------+
| RIC | NAME |
+---------+-------------------------+
| 6758.T | Tokyo Stock Exchange |
| GOOG.O | NASDAQ |
| GS.N | New York Stock Exchange |
| INFY.BO | Bombay Stock Exchange |
+---------+-------------------------+
The above method is called implicit Join an d This query can also be written by using explicit join style which uses ON clause to join tables.
mysql> select s.RIC, m.NAME from Stock s INNER JOIN Market ON m I s.LISTED_ON_EXCHANGE=m.RIC;
14. Calling function on SELECT clause e.g. displaying current date
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2011-10-13 10:25:47 |
+---------------------+
15. Doing calculation using SELECT CLAUSE
You can perform some basic calculations using SELECT clause e.g addition, subtraction, multiplication, division etc.
mysql> select 1+2;
+-----+
| 1+2 |
+-----+
| 3 |
+-----+
16. SELECT data from one row till another row like Paging
If you are thinking to implement paging and getting data from a specified row you can do this easily in Mysql by using the LIMIT clause.
mysql> select * from Stock order by COMPANY LIMIT 0,2;
+--------+-------------------------+--------------------+
| RIC | COMPANY | LISTED_ON_EXCHANGE |
+--------+-------------------------+--------------------+
| GS.N | Goldman Sachs Group Inc | N |
| GOOG.O | Google Inc | O |
+--------+-------------------------+--------------------+
Here first parameter '0' says start from the first record and '2' says to get 2 records only.
17. Selecting data from the result of another query by using a derived table.
Sometimes information needed to produce the final SELECT result comes from another query and acts as a table for the outer SELECT statement. This table is also called the Derived table
mysql> select RIC from (select s.RIC, m.NAME
from Stock s, Market m
where s.LISTED_ON_EXCHANGE=m.RIC) t where RIC > 'G'
+---------+
| RIC |
+---------+
| GOOG.O |
| GS.N |
| INFY.BO |
+---------+Important points about SELECT command in SQL:
So Far we have seen different examples of a SELECT clause in SQL which will enable you to take full advantage of SELECT while writing SQL queries. Here I have listed some important points which you should consider while writing SQL query not just SELECT but with any other keyword also.
1) Most often we use SELECT Operator with WHERE Clause, try to use column which has the index on WHERE clause. Using a non-index column on WHERE clause can slow your query drastically and effect would be more visible when your table data increases. an example with 1 Million records query without index was taking 80-second while after index it just took .3 second, whopping 260% increase in speed.
2) If you don't need all columns, don’t use the * wild card. SELECT query with few columns is slightly faster than all columns.
3) If you are retrieving data from a large table, do a count (*) check before firing actual select query, this will give you en estimate of how many records you are about to get and how much time it could take.
4) You can introduce new columns in the result set of SELECT Query by using the keyword "AS" as shown in the below example. Very useful for displaying calculated value e.g. average or percentage.
5) Always use IS NULL or NULL for including or excluding values which could be null. Don’t use 'null' that will be treated as text.
6) While writing SQL query, not just SELECT, its good practice to write a keyword in the small case and TABLES and COLUMNS in the capital. So that they will stand out from the whole query and makes the query more readable.
That's all on SQL Select command examples, I tried to cover a good number of select command examples to provide an overview of what the SELECT statement can do. If you know any good select example in SQL please share.
Database and Unix Tutorials
what if i have 2 different tables and i want to see what values from table A are NOT in table B?
ReplyDeleteSelect * from tableA where tableA.keyfield not in (select keyfield from tableB)
DeleteOk Good Info..
ReplyDeleteI am rushing a job and doing direct selects on over 2800 rows and need to do various queries to review the db structure before making the the final update statements.
Your tutorial is logical and gives clues as to how to build these queries and I appreciate the effort made here to help us in the dark hours.
Thanks
how to get data from a table & then compare that one with other data
ReplyDeleteI frequently used SELECT clause to generate XML String from my stored procedure. Since SELECT query can print String as it is you can create a combination of static and dynamic String using SELECT command e.g.
ReplyDeleteselect '<name>' + p.name + '</name>' from Person p this will print
<name>James</name>
isn't it great example of SELECT command in SQL ?
Great list of examples for SQL SELECT clause, but you missed couple of them like How to use aggregate function with SELECT query e.g. I can find sum, average, min, max and count in select query without using group by clause :
ReplyDeleteSELECT sum(salary) FROM employee will give you total salary paid, by the way NULL values will be ignored. Similarly
SELECT avg(salary) FROM employee will give you average salary again it will ignore NULL values.
You can also use min(), max() and count() aggregate function in SQL along in SELECT query without group by clause. Cheers
SELECT * FROM A
ReplyDeleteWHERE (field1, field2, ..., fieldN) NOT IN
( SELECT *
FROM B
) ;
plz help me on this
ReplyDeleteEMP(eno,ename,hireDate,job,salary,commission,deptNo)
display departno total salary and total commission for each department that pays at least one employee commission.
@sachita, I am late to the party, but this may help others-->
ReplyDeleteSELECT deptNo, SUM(salary),sum(commission) from EMP GROUP BY deptNo HAVING sum(commission) IS NOT NULL