MySQL SELECT All Data

What is SELECT All Data Statement and How to Use It?

When you want to select all data from a table, use the SELECT * FROM table_name; query. It displays all the records in the table.

Common Mistakes When Using SELECT *

• Do not use this statement for big data. It is better to use it on medium-level data in the table.
• When you use it with big data, you should select specific columns.
• You should use this query carefully. When you need to show data from some columns, you should select only those columns instead of showing all the data. For example:

SELECT name, age FROM students;

This makes the query easier to run and does not waste memory.

Different Forms of SELECT All Data

SELECT All Data with Subquery

A query within another query is called a subquery. In SELECT, a subquery is used to get the result based on a specific condition. A subquery is placed inside the main query.

Syntax:

SELECT *
FROM table_name
WHERE column_name IN (
    SELECT column_name
    FROM another_table
    WHERE condition
);

Example:

SELECT *
FROM learners
WHERE course_id IN (
    SELECT id
    FROM courses
    WHERE course_name = 'MySQL'
);

By executing this query, we select the learners who are enrolled in the course named MySQL.

SELECT All Data with UNION

If you want to fetch all data of a specific column from both tables by removing the duplicate data, the UNION query is used with the SELECT query.

Syntax:

SELECT column_name FROM table1
UNION
SELECT column_name FROM table2;

Example:

Suppose the learners table contains:

name
Rahul
Priya
Amit

And the teachers table contains:

name
Priya
Neha
Ravi

Now, run the given query:

SELECT name FROM learners
UNION
SELECT name FROM teachers;

Output:

name
Rahul
Priya
Amit
Neha
Ravi

After executing this query, the data from the name column of both tables is combined and displayed in the name column. The duplicate data is removed.

SELECT All Data with UNION ALL

If you want to fetch all data of a specific column from both tables without removing duplicate data, the UNION ALL query is used with the SELECT query.

Syntax:

SELECT column_name FROM table1
UNION ALL
SELECT column_name FROM table2;

Example:

Suppose the learners table contains:

name
Rahul
Priya
Amit

And the teachers table contains:

name
Priya
Neha
Ravi

Now, run the given query:

SELECT name FROM learners
UNION ALL
SELECT name FROM teachers;

Output:

name
Rahul
Priya
Amit
Priya
Neha
Ravi

After executing this query, the data from the name column of both tables is combined and displayed in the name column.

SELECT All Rows with WHERE Clause

If you want to fetch all data of a table based on a specific condition, the WHERE clause is used with the SELECT query.

Syntax:

SELECT * FROM table_name
WHERE condition;

Example:

Suppose the learners table contains:

id	name	age
1	Rahul	17
2	Priya	20
3	Amit	18
4	Neha	25

Now, run the given query:

SELECT * FROM learners
WHERE age > 18;

Output:

id	name	age
2	Priya	20
4	Neha	25

After executing this query, all data of the learners whose age is greater than 18 is displayed.

SELECT All Data with ORDER BY

If you want to fetch all data of a table in a specific order, the ORDER BY clause is used with the SELECT query.

Syntax:

SELECT * FROM table_name
ORDER BY column_name ASC;

Example:

Suppose the learners table contains:

id	name	age
1	Rahul	20
2	Amit	22
3	Neha	19
4	Priya	21

Now, run the given query:

SELECT * FROM learners
ORDER BY name ASC;

Output:

id	name	age
2	Amit	22
3	Neha	19
4	Priya	21
1	Rahul	20

After executing this query, all data from the learners table is displayed in ascending order according to the name column.

SELECT All Data with LIMIT

If you want to fetch a specific number of rows from a table, the LIMIT clause is used with the SELECT query.

Syntax:

SELECT * FROM table_name
LIMIT number;

Example:

Suppose the learners table contains:

id	name	age
1	Rahul	20
2	Priya	22
3	Amit	19
4	Neha	21
5	Ravi	23
6	Simran	20
7	Vikas	24

Now, run the given query:

SELECT * FROM learners
LIMIT 5;

Output:

id	name	age
1	Rahul	20
2	Priya	22
3	Amit	19
4	Neha	21
5	Ravi	23

After executing this query, only the first 5 rows of data from the learners table are displayed.

SELECT All Data with DISTINCT

If you want to fetch data of a specific column from a table without showing duplicate data, the DISTINCT keyword is used with the SELECT query.

Syntax:

SELECT DISTINCT column_name
FROM table_name;

Example:

Suppose the learners table has this data: id name city 1 Rahul Delhi 2 Priya Mumbai 3 Amit Delhi 4 Neha Pune 5 Ravi Mumbai

Now, run the given query:

SELECT DISTINCT city
FROM learners;

Output:

city
Delhi
Mumbai
Pune

After executing this query, the unique data from the city column of the learners table is displayed. The duplicate data is removed.

SELECT All Data with JOIN

If you want to fetch related data from two tables, the JOIN query is used with the SELECT query.

Syntax:

SELECT *
FROM table1
JOIN table2
ON table1.column_name = table2.column_name;

Example:

Suppose the learners table has this data:

id	name	course_id
1	Rahul	101
2	Priya	102
3	Amit	101

And the courses table has this data:

id	course_name
101	MySQL
102	PHP
103	HTML

Now, run the given query:

SELECT *
FROM learners
JOIN courses
ON learners.course_id = courses.id;

Output:

id	name	course_id	id	course_name
1	Rahul	101	101	MySQL
2	Priya	102	102	PHP
3	Amit	101	101	MySQL

After executing this query, the related data from the learners and courses tables is combined and displayed.

SELECT All Data with GROUP BY

If you want to fetch data by grouping the same values from a column, the GROUP BY clause is used with the SELECT query.

Syntax:

SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name;

Example:

Suppose the learners table has this data:

id	name	city
1	Rahul	Delhi
2	Priya	Mumbai
3	Amit	Delhi
4	Neha	Pune
5	Ravi	Mumbai
6	Simran	Delhi

Now, run the given query:

SELECT city, COUNT(*)
FROM learners
GROUP BY city;

Output:

city	COUNT(*)
Delhi	3
Mumbai	2
Pune	1

After executing this query, the learners are grouped according to the city column and the number of learners in each city is displayed.

SELECT All Data with HAVING

If you want to fetch grouped data based on a specific condition, the HAVING clause is used with the SELECT query.

Syntax:

SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING condition;

Example:

Suppose, the learners table has this data:

id	name	city
1	Rahul	Delhi
2	Priya	Delhi
3	Amit	Delhi
4	Neha	Delhi
5	Ravi	Delhi
6	Simran	Delhi
7	Vikas	Mumbai
8	Pooja	Mumbai
9	Karan	Mumbai
10	Riya	Mumbai
11	Arjun	Pune
12	Meena	Pune

Now, run the given query:

SELECT city, COUNT(*)
FROM learners
GROUP BY city
HAVING COUNT(*) > 5;

Output:

city	COUNT(*)
Delhi	6

After executing this query, the cities having more than 5 learners are displayed.