The Select ascendancy inwards SQL is ane of the most powerful as well as heavily used commands. This is I guess the get-go ascendancy anyone acquire inwards SQL fifty-fifty earlier CREATE which is used to create a tabular array inwards SQL. SELECT is used inwards SQL to fetch records from database tables as well as yous tin do a lot many things using Select. For example, yous tin select all records, yous tin select few records based on the status specified inwards WHERE clause, select all columns using the wild carte du jour (*) or entirely selecting a few columns yesteryear explicitly declaring them inwards a query.
In this SELECT SQL ascendancy tutorial, nosotros volition encounter but about examples of select command or Select Statement as well as volition write SQL queries to demonstrate the result. We volition utilization next tabular array as well as information for our SQL question examples, ane tabular array stand upward for Stocks listed inwards diverse marketplace as well as but about other tabular array contains Details of marketplace e.g. Country. MySQL is my favorite RDBMS as well as smashing for learning role yous tin download MySQL as well as start working on it. My proffer is to utilization ascendancy trace of piece of job interface for writing queries instead of using GUI e.g. SQL Developer or MySQL question tool. Command trace of piece of job is best for learning as well as existent fun of writing SQL question is entirely on the ascendancy 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 inwards develop (0.00 sec)
mysql> select * from MARKET;
+------+-------------------------+---------------+
| RIC | NAME | COUNTRY |
+------+-------------------------+---------------+
| T | Tokyo Stock Exchange | Japan |
| O | NASDAQ | U.S.A. of America |
| N | New York Stock Exchange | U.S.A. of America |
| BO | Mumbai Stock Exchange | India |
+------+-------------------------+---------------+
4 rows inwards develop (0.00 sec)
SQL SELECT ascendancy question examples
hither are but about of my favorite select clause examples which explore dissimilar ways ane tin utilization the select ascendancy for reporting role as well as display results.
1) Finding how many rows inwards tables
mysql> select count(*) from STOCK;
+----------+
| count(*) |
+----------+
| v |
+----------+
2) Finding all records from tables; nosotros 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 inwards develop (0.00 sec)
3. Selecting few records based on but about status from tables inwards 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 but give the advert 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
The distinct keyword is used to exhibit entirely unique records it volition non exhibit whatever duplicate values.
mysql> select distinct LISTED_ON_EXCHANGE from Stock;
+--------------------+
| LISTED_ON_EXCHANGE |
+--------------------+
| T |
| O |
| N |
| BO |
| L |
+--------------------+
6. Selecting value with status 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 status using logical operator AND & OR
AND as well as OR Can live on effectively used to combine 2 weather condition on WHERE clause as well as gives yous a 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 tin position whatever expose of AND, OR weather condition on WHERE Clause, sometimes things choke quite slow when yous combine AND, OR inwards SQL.
8. How to uncovering records which are non zero using keyword NULL as well as IS NULL
NULL is really tricky inwards SQL; NULL agency anything which doesn't guide keep value. NULL is non "null" which volition live on treated every bit text.To demonstrate this nosotros volition insert a Stock which is non listed on whatever 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 inwards develop (0.00 sec)
You See in that location is entirely ane row who has LISTED_ON_EXCHANGE null, nosotros volition immediately encounter count using NULL as well as IS NULL which volition verify this result.
mysql> select count(*) from STOCK where LISTED_ON_EXCHANGE IS NULL;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row inwards develop (0.00 sec)
mysql> select count(*) from STOCK where LISTED_ON_EXCHANGE IS NOT NULL;
+----------+
| count(*) |
+----------+
| v |
+----------+
1 row inwards develop (0.00 sec)
mysql> select count(*) from STOCK;
+----------+
| count(*) |
+----------+
| half dozen |
+----------+
1 row inwards develop (0.00 sec)
9. SELECT Statement using BETWEEN as well as NOT BETWEEN
As the advert advise BETWEEN is used to acquire information betwixt 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 inwards SQL queries using LIKE as well as NOT LIKE
LIKE is a blueprint matching operator as well as used to uncovering records which are non an exact tally but belike 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 as well as display records which are non belike 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 as well as NOT IN
IN is but about other useful SQL operator nosotros tin utilization amongst SELECT. it provides a develop of values which tin live on used inwards WHERE clause.
mysql> select * from Stock where RIC inwards ('GS.N' , 'INFY.BO');
+---------+-------------------------+--------------------+
| RIC | COMPANY | LISTED_ON_EXCHANGE |
+---------+-------------------------+--------------------+
| GS.N | Goldman Sachs Group Inc | N |
| INFY.BO | InfoSys | BO |
+---------+-------------------------+--------------------+
12. Sorting ResultSet inwards SQL using ORDER BY, ASC, DESC
Order yesteryear is used to variety records inwards the resultant develop returned yesteryear SELECT clause. By default, it listing inwards Ascending social club but nosotros tin utilization either ascending or descending using specifier ASC as well as DESC.
mysql> select * from Stock social club yesteryear 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 information from multiple tables yesteryear using JOIN inwards SQL
Join inwards SQL is a powerful concept which allows yous to select information from multiple tables. You tin generate a written report where information is accumulated from dissimilar tables based on weather condition specified inwards Join statement.
Suppose yous scream for to “display a listing of Records as well as Name of Market where they are listed”. Here the advert of Stock inwards the STOCK tabular array spell the advert of telephone substitution inwards the MARKET table. We scream for to bring together both of them to display this report.
mysql> select s.RIC, m.NAME from Stock s, Market k 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 | Mumbai Stock Exchange |
+---------+-------------------------+
Above method is called implicit Join an d This question tin also live on written yesteryear using explicit bring together fashion which uses ON clause to bring together tables.
mysql> select s.RIC, m.NAME from Stock s INNER JOIN Market ON k I s.LISTED_ON_EXCHANGE=m.RIC;
15. Calling component division on SELECT clause e.g. displaying electrical flow date
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2011-10-13 10:25:47 |
+---------------------+
16. Doing calculation using SELECT CLAUSE
You tin perform but about basic calculation using SELECT clause e.g addition, subtraction, multiplication, sectionalization etc.
mysql> select 1+2;
+-----+
| 1+2 |
+-----+
| three |
+-----+
17. SELECT information from ane row till but about other row similar Paging
If yous are thinking to implement paging as well as getting information from specified row yous tin do this easily inwards Mysql yesteryear using LIMIT clause.
mysql> select * from Stock social club yesteryear COMPANY LIMIT 0,2;
+--------+-------------------------+--------------------+
| RIC | COMPANY | LISTED_ON_EXCHANGE |
+--------+-------------------------+--------------------+
| GS.N | Goldman Sachs Group Inc | N |
| GOOG.O | Google Inc | O |
+--------+-------------------------+--------------------+
Here get-go parameter '0' says start from the get-go tape as well as '2' says to acquire 2 records only.
18. Selecting information from the resultant of but about other question yesteryear using derived table.
Sometimes information needed to attain finally SELECT resultant comes from but about other question as well as human activity every bit a tabular array for the outer SELECT statement. This tabular array also called Derived table
mysql> select RIC from (select s.RIC, m.NAME from Stock s, Market k where s.LISTED_ON_EXCHANGE=m.RIC) t where RIC > 'G'
+---------+
| RIC |
+---------+
| GOOG.O |
| GS.N |
| INFY.BO |
+---------+
Some Important request most SELECT ascendancy inwards SQL:
So Far nosotros guide keep seen dissimilar examples of a SELECT clause inwards SQL which volition enable yous to guide keep amount wages of SELECT spell writing SQL queries. Here I guide keep listed but about of import points which yous should consider spell writing SQL question non but SELECT but with whatever other keyword also.
1) Most oftentimes nosotros utilization SELECT Operator with WHERE Clause, stimulate to utilization column which has the index on WHERE clause. Using a non-index column on WHERE clause tin ho-hum your question drastically as well as lawsuit would live on to a greater extent than visible when your tabular array information increases. an illustration with 1 Million records question without index was taking 80-second spell afterward index it but took .3 second, whopping 260% increment inwards speed.
2) If yous don't scream for all columns, don’t utilization the * wild card. SELECT question with few columns is slightly faster than all columns.
3) If yous are retrieving information from a large table, do a count (*) cheque earlier firing actual select query, this volition give yous en justice of how many records yous are most to acquire as well as how much fourth dimension it could take.
4) You can innovate novel columns inwards the resultant develop of SELECT Query yesteryear using keyword "AS" as shown inwards 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 live on null. Don’t utilization 'null' that volition live on treated every bit text.
6) While writing SQL query, non but SELECT, its skillful exercise to write a keyword inwards the small-scale instance and TABLES as well as COLUMNS inwards capital. So that they volition stand upward out from the whole question as well as makes the question to a greater extent than readable.
That's all on SQL Select ascendancy examples, I tried to encompass a skillful expose of select ascendancy illustration to supply an overview what SELECT contestation tin do. If yous know whatever skillful select illustration inwards SQL delight share.
Further Learning
Difference betwixt truncate as well as delete inwards SQL