Friday, October 14, 2011

10 Example Queries of SQL Select Command

Select command in SQL is one of the most powerful and heavily used commands. This is I guess the first command any one learn in SQL even before CREATE which is used to create 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 condition specified in WHERE clause, select all columns using wild card (*) or only selecting few columns by explicitly declaring them in query.

select command sql example, select sql 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 following table and data for our SQL query examples, one table represent Stocks listed in various market and other table contains Details of market e.g. Country. MySQL is my favorite RDBMS and great for learning purpose you can download MySQL and start working on it. My suggestion is to use 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 real fun of writing SQL query is only on 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)

SQL SELECT command query examples

here are some of my favorite select clause examples which explores different ways one can use select command for reporting purpose and display results.
1) Finding how many rows in tables

mysql> select count(*) from STOCK;
+----------+
| count(*) |
+----------+
|        5 |
+----------+

2) Finding all records from tables; we are using wildcard start * for getting all 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 few records based on some condition from tables in SQL

mysql> select * from STOCK where RIC='GOOG.O';
+--------+------------+--------------------+
| RIC    | COMPANY    | LISTED_ON_EXCHANGE |
+--------+------------+--------------------+
| GOOG.O | Google Inc | O                  |
+--------+------------+--------------------+


4. How to select few columns instead of all columns?
Instead of using start wild-card just give name of interested columns to SELECT clause.

mysql> select COMPANY from STOCK where RIC='GOOG.O';
+------------+
| COMPANY    |
+------------+
| Google Inc |
+------------+

5. Select distinct (unique) records from Columns
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.

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 WHERE clause and gives you lot of flexibility to write SQL query.

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, some time things become quite easy when you combine AND, OR in SQL.


8. How to find records which is 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)

You 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 name suggest BETWEEN is used to get data between a ranges.

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 which are not exact match but probable match.

mysql> select * from Stock where RIC LIKE 'V%';
+-------+--------------------+--------------------+
| RIC   | COMPANY            | LISTED_ON_EXCHANGE |
+-------+--------------------+--------------------+
| VOD.L | Vodafone Group PLC | L                  |
+-------+--------------------+--------------------+

NOT LIKE is opposit 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 set of values which can be used in WHERE cluase.

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 result set returned by SELECT clause. By default it list 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                  |
+---------+-------------------------+--------------------+


14. Selecting data from multiple tables by using JOIN in SQL
Join in SQL is powerful concept which allows you to select data from multiple tables. You can generate report where data is accumulated from different tables based on conditions specified in Join statement.

Suppose you need to “display list of Records and Name of Market where they are listed”. Here name of Stock in STOCK table while name of exchange in 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   |
+---------+-------------------------+

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;



15. Calling function on SELECT clause e.g. displaying current date

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2011-10-13 10:25:47 |
+---------------------+

16. Doing calculation using SELECT CLAUSE
You can perform some basic calculation using SELECT clause e.g addition, subtraction, multiplication, division etc.

mysql> select 1+2;
+-----+
| 1+2 |
+-----+
|   3 |
+-----+


17. SELECT data from one row till another row like Paging
If you are thinking to implement paging and getting data from specified row you can do this easily in Mysql by using 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 first record and '2' says get 2 record only.

18. Selecting data from result of another query by using derived table.
Sometime information needed to produce final SELCT result comes from another query and act as table for outer SELECT statement. This table also called 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 |
+---------+


Some Important point about SELECT command in SQL:


So Far we have seen different examples of 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 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. a 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 * wild card. SELECT query with few columns are slightly faster than all columns.

3) If you are retrieving data from 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 result set of SELECT Query by using keyword "AS" as shown in 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 keyword in small case and TABLES and COLUMNS in capital. So that they will stand out from whole query and makes query more readable.

That's all on SQL Select command examples, I tried to cover good number of select command example to provide an overview what SELECT statement can do. If you know any good select example in sql please share.

Database and Unix Tutorials

11 comments :

Anonymous said...

what if i have 2 different tables and i want to see what values from table A are NOT in table B?

Steve Rom said...

Ok Good Info..
I 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

Anonymous said...

how to get data from a table & then compare that one with other data

James said...

I 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.

select '<name>' + p.name + '</name>' from Person p this will print
<name>James</name>

isn't it great example of SELECT command in SQL ?

Yohana said...

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 :

SELECT 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

Amit Garg said...

SELECT * FROM A
WHERE (field1, field2, ..., fieldN) NOT IN
( SELECT *
FROM B
) ;

Anonymous said...

Select * from tableA where tableA.keyfield not in (select keyfield from tableB)

sachita said...

plz help me on this
EMP(eno,ename,hireDate,job,salary,commission,deptNo)
display departno total salary and total commission for each department that pays at least one employee commission.

Anonymous said...

do u tell me in sql good future provide

Anonymous said...

do u tell me in sql good salary provide

venkat said...

@sachita, I am late to the party, but this may help others-->

SELECT deptNo, SUM(salary),sum(commission) from EMP GROUP BY deptNo HAVING sum(commission) IS NOT NULL

Post a Comment