Updated May 10, 2023
Introduction to SELECT in MySQL
In this topic, we will learn about SELECT in MySQL and mostly DQL, which is “Data Query Language”. This comes into play when we try to fetch records from the database, starting with the “SELECT” command. This command can be used with many SQL clauses and other functions to get desired records.
There are many kinds of SQL commands which can be categorized into the following:
- DDL (Data definition language)
- DML (Data manipulation language)
- DQL (Data query language)
- DCL (Data control language)
- TCL (Transaction control language)
Syntax:
1. The basic syntax of the SELECT command.
SELECT * FROM table_name;This will fetch all the records with all attributes from a table.
SELECT column1, column2,…. FROM table_name;This will fetch specified columns from a table passed through the query.
2. This SELECT command can also be used with the INSERT command, which is used for adding records to the existing table.
INSERT INTO table_name1 SELECT * FROM table_name2;Here query will fetch all the records from table_name2 and will insert those into table_name1.
Examples to Implement SELECT in MySQL
Let’s consider there is a customer table with the following attributes.
| Cust_id | First_name | Last_name | Contact | City | Amount | |
| 1001 | Rohit | Sharma | 9876736587 | [email protected] | Mumbai | 10000 |
| 1002 | Virat | Kohli | 8752877855 | [email protected] | Delhi | 60000 |
| 1003 | Sachin | Tendulkar | 9867868678 | [email protected] | Mumbai | 15000 |
| 1004 | Virendra | Shewag | 9087788988 | [email protected] | Delhi | 20000 |
We will see some basic SELECT queries using some clauses to understand how this command works.
Example #1
Command:
SELECT * FROM customer;(This will fetch all the records with all attributes from a table)
Output:
| Cust_id | First_name | Last_name | Contact | City | Amount | |
| 1001 | Rohit | Sharma | 9876736587 | [email protected] | Mumbai | 10000 |
| 1002 | Virat | Kohli | 8752877855 | [email protected] | Delhi | 60000 |
| 1003 | Sachin | Tendulkar | 9867868678 | [email protected] | Mumbai | 15000 |
| 1004 | Virendra | Shewag | 9087788988 | [email protected] | Delhi | 20000 |
Example #2
Command:
SELECT cust_id, first_name, last_name, email,city
FROM customer;(This will fetch specified columns from a table that are passed through a query)
Output:
| Cust_id | First_name | Last_name | City | |
| 1001 | Rohit | Sharma | [email protected] | Mumbai |
| 1002 | Virat | Kohli | [email protected] | Delhi |
| 1003 | Sachin | Tendulkar | [email protected] | Mumbai |
| 1004 | Virendra | Shewag | [email protected] | Delhi |
Example #3
Command:
SELECT cust_id, first_name, last_name, email,city FROM customer
WHERE city = 'Delhi';(WHERE command will fetch those records only, where the city will be ‘Delhi’)
Output:
| Cust_id | First_name | Last_name | City | |
| 1002 | Virat | Kohli | [email protected] | Delhi |
| 1004 | Virendra | Shewag | [email protected] | Delhi |
Example #4
Command:
SELECT cust_id, first_name, last_name,city, amount FROM customer
WHERE amount BETWEEN 5000 AND 25000;(BETWEEN clause will return records which satisfy the given range of conditions passed in the query)
Output:
| Cust_id | First_name | Last_name | City | Amount |
| 1001 | Rohit | Sharma | Mumbai | 10000 |
| 1003 | Sachin | Tendulkar | Mumbai | 15000 |
| 1004 | Virendra | Shewag | Delhi | 20000 |
Example #5
Command:
SELECT * FROM customer
ORDER BY amount DESC;(Used to sort numeric and string values in ascending or descending manner. But by default, it sorts in an ascending manner. If we want it in descending, then we need to specify it after using the ORDER BY clause)
Output:
| Cust_id | First_name | Last_name | Contact | City | Amount | |
| 1002 | Virat | Kohli | 8752877855 | [email protected] | Delhi | 60000 |
| 1004 | Virendra | Shewag | 9087788988 | [email protected] | Delhi | 20000 |
| 1003 | Sachin | Tendulkar | 9867868678 | [email protected] | Mumbai | 15000 |
| 1001 | Rohit | Sharma | 9876736587 | [email protected] | Mumbai | 10000 |
Clauses with SELECT Command
Other clauses with SELECT command:
1. SELECT: Used to fetch all the records from a table.
SELECT * FROM table;2. DISTINCT: Used to fetch all the unique values from a table.
SELECT DISTINCT col_name FROM table;3. WHERE: Used forgiving conditions in the retrieval of records.
SELECT employee_id FROM employee
WHERE name = 'stella';4. COUNT: Used to get the number of records present in a table.
SELECT COUNT(*) FROM employee;5. ORDER BY: Used to sort numeric and string values in ascending or descending manner. But by default, it sorts in an ascending way. If we want to descend, we need to specify it after using the ORDER BY clause.
SELECT first_name FROM student
ORDER BY marks desc;6. LIMIT: This is used to specify the number of records we want after executing the query. If we wish to the top 5 students of a class, then after sorting the results, we can use this LIMIT by specifying five so that it will only fetch the top 5 records.
SELECT first_name FROM student
ORDER BY marks desc
LIMIT 5;(**ORDER BY used here for sorting value in descending order)
7. AND: If two conditions are given, and both are met for a record, then only the query will fetch that record.
SELECT employee_id FROM employee
WHERE name = 'stella' AND city = 'Bangalore';8. OR: If two conditions are given, and one is met for a record, then that record will be fetched.
SELECT employee_id FROM employee
WHERE department = 'IT' OR city = 'Bangalore';9. NOT: Used with conditions. If we specify NOT before any conditions, records that are not meeting those conditions will be fetched.
SELECT employee_id FROM employee
WHERE NOT BETWEEN 1 AND 10;10. BETWEEN: This operator selects records within a given range. Mostly we use this where we want to specify a range of dates.
SELECT emp_id FROM employee
WHERE emp_id BETWEEN 1 AND 10;SELECT * FROM employee
WHERE join_date BETWEEN '2007-01-01' AND '2008-01-01';11. IN: This operator allows us to specify multiple values in a WHERE clause.
SELECT * FROM employee
WHERE employee_id IN (1001,1004,1008,1012);12. LIKE: This operator uses the WHERE clause to search for a specified pattern in a string column.
- ‘A%’ – String starts with A.
- ‘&A’ – Ends with A.
- ‘%A%’ – A will be in between the string.
- ‘_A%’ – Here the 2nd letter will be A.
- ‘%A_’ – The 2nd from the last letter will be A.
SELECT first_name FROM table
WHERE first_name LIKE 'A%';13. SUBSTRING: Used to pick a specific character from a string by specifying the position.
SELECT SUBSTRING(customer_name,1,5) FROM customer_table;(It will fetch characters from the 1st to 5th position of a string)
14. INSTR: This returns a position of a string in another string.
SELECT INSTR('independence', 'pen');(It will find the position of ‘pen’ in the word ‘independence’)
15. GROUP BY: This segregates records based on some given conditions.
SELECT employee_id FROM employee GROUP BY department HAVING salary > 100000;(Here, group by segregated employees based on their department and whose salary is more than 100k.
Condition always comes with a HAVING statement in the GROUP BY clause.)
Aggregate Functions
Below are the different aggregate functions:
1. SUM: Calculates the sum of values.
SELECT SUM(salary) FROM employee;2. AVG: Calculates the average set of values.
SELECT AVG(salary) FROM employee;3. MIN: Gets the minimum value in a set of values.
SELECT MIN(salary) FROM employee;4. MAX: Gets the maximum value in a set of values.
SELECT MAX(salary) FROM employee;Joins in SELECT in MySQL
Given below are the joins in SELECT in MySQL:
1. INNER JOIN: Returns records with matching values in both tables.
SELECT * FROM order
INNER JOIN customer
ON order.cust_id = customer.cust_id;
2. LEFT JOIN: Returns all records from the left table and the matched records from the right table.
SELECT * FROM order
LEFT JOIN customer
ON order.cust_id = customer.cust_id;
3. RIGHT JOIN: Returns all records from the right table and the matched records from the left table.
SELECT * FROM order
RIGHT JOIN customer
ON order.cust_id = customer.cust_id;
4. FULL OUTER JOIN: Returns all the records when a match is on the left or right table.
SELECT * FROM order
FULL OUTER JOIN customer
ON order.cust_id = customer.cust_id;
Conclusion
These commands and clauses we discussed above are very useful in real-time scenarios as they provide the basic concepts of using SQL queries to fetch and manipulate data in the database. Apart from this, these clauses are essential for using advanced and analytical queries like the window function.
Recommended Articles
We hope that this EDUCBA information on “SELECT in MySQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
