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.
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
Tidak ada komentar:
Posting Komentar