DEV Community

nadirbasalamah
nadirbasalamah

Posted on • Updated on

MySQL tutorial - 4 Selecting the data

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
Enter fullscreen mode Exit fullscreen mode

To retrieve all data from all column in a table, use the asterisk notation (*).

SELECT * FROM shop;
Enter fullscreen mode Exit fullscreen mode

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 |
+----+---------------+----------+-------+
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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 |
+---------------+-------+
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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 |
+----+--------------+----------+-------+
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

This is the output from the query above.

+----+---------------+----------+-------+
| id | product_name  | quantity | price |
+----+---------------+----------+-------+
|  1 | Mango         |       90 |   7.8 |
|  4 | Fresh Chicken |       25 |  10.3 |
+----+---------------+----------+-------+
Enter fullscreen mode Exit fullscreen mode

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));
Enter fullscreen mode Exit fullscreen mode

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");
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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 |
+-------------+---------------+----------+
Enter fullscreen mode Exit fullscreen mode

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";
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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");
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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     |
+-------------+---------------+----------+
Enter fullscreen mode Exit fullscreen mode

This is the illustration of left join using posts and users table.

Left join

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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 |
+-------------+---------------+----------+
Enter fullscreen mode Exit fullscreen mode

This is the illustration of right join using posts and users table.

Right join

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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 |
+-------------+---------------+----------+
Enter fullscreen mode Exit fullscreen mode

This is the illustration of full join using posts and users table.

Full join

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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 |
+----+---------------+----------+-------+
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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 |
+----+---------------+----------+-------+
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

This is the output from the query above.

+-----------------+----------+
| number_of_posts | username |
+-----------------+----------+
|               1 | harisson |
|               2 | nathan   |
|               2 | test     |
+-----------------+----------+
Enter fullscreen mode Exit fullscreen mode

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.

Discussion (0)