SELECT Query in SQL
SQL (Structured Query Language) is the cornerstone of database management. It allows users to retrieve and manipulate data stored in relational databases. that means SQL allows users to perform CURD operation on database. CURD is fundamental operation of SQL.
Among these fundamental operations, SELECT query is used to fetch data from database. you can specify criteria of fetching data , can also apply some other operations like grouping , ordering etc.
Syntax
SELECT column1, column2, ...... FROM table_name;
- SELECT Clause is used to specifies the columns to be retrieved from the database table.
- FROM Clause is used to specifies the tables from which to retrieve data.
Lets assume we have 2 tables `Users` and `UserAddress`. Users has Id, Name , Mobile ,DOB and UserAddress has Id, UserId, AddressType, Address , Country , City and PostalCode colums
Selecting all data and all columns from Users table
If you want to fetch all columns from a table you can use * . This will fetch all columns from a table without specifying columns name in query.
SELECT * FROM Users;
Selecting specifies columns from Users table.
If you want to fetch specifies columns from a table you have to mention columns name in sql SELECT query.
SELECT Id, Name FROM Users;
SELECT with WHERE
if you want to fetch data from table based on certain condition. you have to use WHERE clause. in where clause you can specify your condition.
SQL query to fetch users which mobile number start with 987.
SELECT Id, Name, Mobile FROM Users WHERE Mobile like "987%";
SELECT with ORDER BY
if you want to fetch data from table in ascending or descending order . you have to use ORDER BY clause. The ORDER BY clause sort the result set based on one or more columns, either in ascending (ASC) or descending (DESC) order that you specify in query. Default order of fetching data is ASC.
SQL query to fetch users that are sorted in name as ascending and DOB as descending order.
SELECT Id, Name, DOB FROM Users ORDER BY Name ASC, DOB DESC;
OR
SELECT Id, Name, DOB FROM Users ORDER BY Name , DOB DESC;SELECT with LIMIT
if you want to fetch n rows of data from table. you should use LIMIT clause.
SQL query for fetching 4 users.
SELECT Id, Name FROM Users LIMIT 4;
SELECT with JOIN
if you want to fetch data from different tables. you should use JOIN clause.
SQL query for fetching users which Permanent address is India.
SELECT u.Id, u.Name, ua.Address, ua.AddressType, ua.Country FROM Users as u JOIN UserAddress as ua on u.Id=ua.UserId WHERE AddressType = "Pre" and Country = "india";
SELECT with Multiple Clause
if you want to fetch data where you need to apply multiple clause. you can write a single query with multiple clause based on your requirement below is the simple query structure of such cases.
SELECT column1, column2,...... FROM table_name WHERE columnX like "___" order by columnY LIMIT n;