MySQL tutorial - 4 Selecting the data
Tags: mysql, beginners, tutorial
Published: 2021-03-05 00:20:53

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 (`*`).

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
-- 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.

```sql
-- 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`.

```sql
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.

```sql
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`.

```sql
-- 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.

```sql
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.

![Left join](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zcheskjxd3nivypjptmb.png)

### 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.

```sql
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.

![Right join](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9lqv0oryz8478ln0hvr1.png)

### 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.

```sql
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.

![Full join](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tyb4lljgdo2xeq7z4da4.png)

## 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.

```sql
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.

```sql
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.

```sql
-- 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](https://www.w3schools.com/sql/sql_join.asp).

I hope this article is helpful for learning SQL, If you have any thoughts or comments you can write in the discussion section below.