nadirbasalamah
Posted on March 5, 2021
In MySQL, to view or retrieve all of the data that exists in a table can be done using SELECT query. This query can retrieve all of the data or a specific data based on the given condition.
SELECT query
This is the basic structure of SELECT query.
SELECT column_name, column_name FROM table_name
To retrieve all data from all column in a table, use the asterisk notation (*).
SELECT * FROM shop;
This is the output example from the query above.
+----+---------------+----------+-------+
| id | product_name | quantity | price |
+----+---------------+----------+-------+
| 1 | Mango | 90 | 7.8 |
| 2 | Milk | 100 | 8.8 |
| 3 | Apple | 110 | 7.8 |
| 4 | Fresh Chicken | 25 | 10.3 |
| 5 | Corn Flakes | 19 | 2.99 |
+----+---------------+----------+-------+
To retrieve all data from certain column, define the column that want to be retrieved besides SELECT. For this example, the columns called product_name and price will be retrieved.
SELECT product_name, price FROM shop;
This is the output example from the query above, notice that the product_name and price is retrieved.
+---------------+-------+
| product_name | price |
+---------------+-------+
| Mango | 7.8 |
| Milk | 8.8 |
| Apple | 7.8 |
| Fresh Chicken | 10.3 |
| Corn Flakes | 2.99 |
+---------------+-------+
SELECT with WHERE clause
To retrieve specific data based on a given condition can be done using additional clause with WHERE. This is the basic structure of SELECT query with WHERE clause.
SELECT column_name, column_name FROM table_name WHERE condition
In this example, The data that will be retrieved is a data that has a price less than 10.
SELECT * FROM shop WHERE price < 10;
This is the output from the query above.
+----+--------------+----------+-------+
| id | product_name | quantity | price |
+----+--------------+----------+-------+
| 1 | Mango | 90 | 7.8 |
| 2 | Milk | 100 | 8.8 |
| 3 | Apple | 110 | 7.8 |
| 5 | Corn Flakes | 19 | 2.99 |
+----+--------------+----------+-------+
The condition in WHERE clause can be combined with AND and also OR clause just like conditional statement in programming language. In this example, the data that will be retrieved is a data that has quantity less than 100 and a price more than 5.
SELECT * FROM shop WHERE quantity < 100 AND price > 5;
This is the output from the query above.
+----+---------------+----------+-------+
| id | product_name | quantity | price |
+----+---------------+----------+-------+
| 1 | Mango | 90 | 7.8 |
| 4 | Fresh Chicken | 25 | 10.3 |
+----+---------------+----------+-------+
SELECT with JOIN clause
In MySQL, data from each table can be retrieved by using JOIN clause. For this example, there is a posts table that has a relation with the users table. This relation means that a post is created by an user.
-- create users table
CREATE TABLE users(id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(255), email TEXT);
-- create posts table
CREATE TABLE posts (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
title TEXT NOT NULL,
content TEXT NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id));
Let's add some data inside users and posts table.
-- insert some users data
INSERT INTO users VALUES(0,"test","test@test.com");
INSERT INTO users VALUES(0,"nathan","nathan@test.com");
INSERT INTO users VALUES(0,"harisson","harris@test.com");
-- after users data is inserted, use this query
-- insert some posts data
INSERT INTO posts VALUES(0,1,"title one","content one");
INSERT INTO posts VALUES(0,1,"title two","content two");
INSERT INTO posts VALUES(0,2,"title three","content three");
INSERT INTO posts VALUES(0,2,"title four","content four");
INSERT INTO posts VALUES(0,3,"title five","content five");
There are many types of join operation including inner join, outer join, left join and right join.
INNER JOIN
Inner join is a type of join operation that joins two or more tables with certain condition. This join operation is commonly used in many use cases. This is the basic query structure of inner join.
SELECT column_name FROM table_name JOIN other_table_name ON conditions
For this example, the table called posts and users joined together with condition posts.user_id is equals with users.id.
SELECT posts.title, posts.content, users.username FROM posts JOIN users ON posts.user_id = users.id;
This is the output of the query above. Notice that the post's data including title and content are retrieved together with the user's name that created the post.
+-------------+---------------+----------+
| title | content | username |
+-------------+---------------+----------+
| title one | content one | test |
| title two | content two | test |
| title three | content three | nathan |
| title four | content four | nathan |
| title five | content five | harisson |
+-------------+---------------+----------+
The WHERE clause can be used together with join operation. This is the example.
SELECT posts.title, posts.content, users.username FROM posts JOIN users ON posts.user_id = users.id WHERE users.username = "nathan";
LEFT JOIN
Left join or left outer join is a type of join operation that joins two tables if the left table has the available data in the right table. Otherwise, if the data is not available in the right table, it returned NULL. This is the structure of left join query.
SELECT column_name FROM table_name LEFT JOIN other_table_name ON conditions
For this example, let's add a new post data without user_id.
-- add post without user_id
INSERT INTO posts (id, title, content) VALUES (0, "cool title","cool content");
Then, execute this query, this query joins posts and users table using left join.
SELECT posts.title, posts.content, users.username FROM posts LEFT JOIN users ON posts.user_id = users.id;
This is the result from query above, notice that the new post with the title cool title has a username equals NULL because the user_id is not available or empty in this data.
+-------------+---------------+----------+
| title | content | username |
+-------------+---------------+----------+
| title one | content one | test |
| title two | content two | test |
| title three | content three | nathan |
| title four | content four | nathan |
| title five | content five | harisson |
| cool title | cool content | NULL |
+-------------+---------------+----------+
This is the illustration of left join using posts and users table.
RIGHT JOIN
Right join or right outer join is a type of join operation that joins two tables if the right table has the available data in the left table. This is the structure of right join query.
SELECT column_name FROM table_name RIGHT JOIN other_table_name ON conditions
In this example, this query joins posts and users table using right join.
SELECT posts.title, posts.content, users.username FROM posts RIGHT JOIN users ON posts.user_id = users.id;
This is the result from query above, notice that the new post with the title cool title is not retrieved because the user_id is empty or not available in the users table.
+-------------+---------------+----------+
| title | content | username |
+-------------+---------------+----------+
| title one | content one | test |
| title two | content two | test |
| title three | content three | nathan |
| title four | content four | nathan |
| title five | content five | harisson |
+-------------+---------------+----------+
This is the illustration of right join using posts and users table.
FULL JOIN (UNION ALL)
Full join is a type of join operation that joins two tables if the right table and left table has available data in each table. Basically in MySQL, full join operation can be done by using left join and right join combined together with UNION ALL operation. The query structure looks like this.
SELECT column_name FROM table_name LEFT JOIN other_table_name ON conditions
UNION ALL
SELECT column_name FROM table_name RIGHT JOIN other_table_name ON conditions;
In this example, this query joins posts and users table using full join.
SELECT posts.title, posts.content, users.username FROM posts LEFT JOIN users ON posts.user_id = users.id
UNION ALL
SELECT posts.title, posts.content, users.username FROM posts RIGHT JOIN users ON posts.user_id = users.id;
This is the result from query above. Notice that this result is a combination between left join and right join.
+-------------+---------------+----------+
| title | content | username |
+-------------+---------------+----------+
| title one | content one | test |
| title two | content two | test |
| title three | content three | nathan |
| title four | content four | nathan |
| title five | content five | harisson |
| cool title | cool content | NULL |
| title one | content one | test |
| title two | content two | test |
| title three | content three | nathan |
| title four | content four | nathan |
| title five | content five | harisson |
+-------------+---------------+----------+
This is the illustration of full join using posts and users table.
SELECT with grouping
Retrieving or selecting data from a table can be grouped based on the specific criteria. The order's criteria of the data from the table can be specified using ORDER BY clause.
SELECT column_name FROM table_name ORDER BY column_name order_type
In this example, the data from the shop table is retrieved based on the product's name in alphabetical order.
SELECT * FROM shop ORDER BY product_name ASC;
This is the output from the query above.
+----+---------------+----------+-------+
| id | product_name | quantity | price |
+----+---------------+----------+-------+
| 3 | Apple | 110 | 7.8 |
| 5 | Corn Flakes | 19 | 2.99 |
| 4 | Fresh Chicken | 25 | 10.3 |
| 1 | Mango | 90 | 7.8 |
| 2 | Milk | 100 | 8.8 |
+----+---------------+----------+-------+
Another example is retrieve all of the data from shop table based on the price order from the highest price into the lowest.
SELECT * FROM shop ORDER BY price DESC;
This is the output from the query above.
+----+---------------+----------+-------+
| id | product_name | quantity | price |
+----+---------------+----------+-------+
| 4 | Fresh Chicken | 25 | 10.3 |
| 2 | Milk | 100 | 8.8 |
| 1 | Mango | 90 | 7.8 |
| 3 | Apple | 110 | 7.8 |
| 5 | Corn Flakes | 19 | 2.99 |
+----+---------------+----------+-------+
The another way of data grouping in MySQL is using GROUP BY clause. This clause is usually used together with some aggregate functions (for example COUNT,SUM,AVG). In this example, the number of posts written by each user will be calculated using COUNT function. Notice that GROUP BY can be used together with join operation.
-- the "AS" clause is used to give an alias from certain column, in this case the COUNT result is stored in a column called number_of_posts.
SELECT COUNT(posts.id) AS number_of_posts, users.username FROM posts JOIN users ON posts.user_id = users.id GROUP BY users.username;
This is the output from the query above.
+-----------------+----------+
| number_of_posts | username |
+-----------------+----------+
| 1 | harisson |
| 2 | nathan |
| 2 | test |
+-----------------+----------+
Notes
- Other examples of SQL JOIN can be checked here.
I hope this article is helpful for learning SQL, If you have any thoughts or comments you can write in the discussion section below.
Posted on March 5, 2021
Join Our Newsletter. No Spam, Only the good stuff.
Sign up to receive the latest update from our blog.
Related
