MySQL Column Alias and Sub-query

Create TABLES

mysql> CREATE TABLE item
    -> (
    -> item_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> item varchar(200) NOT NULL,
    -> price double NOT NULL);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE customer
    -> (
    -> customer_id INT NOT NUL AUTO_INCREMENT PRIMARY KEY,
    -> name VARCHAR(200) NOT NULL
    -> );
mysql> CREATE TABLE customer_order 
    -> ( 
    -> order_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    -> customer_id INT NOT NULL, 
    -> item_id INT NOT NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

Insert Sample Records

mysql> INSERT INTO customer VALUES(null,'Janny'),
    -> (null,'Bob'),
    -> (null,'Steven');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO item VALUES(null,'CD',5),
    -> (null,'PHONE',45),
    -> (null,'Laptop',880);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO customer_order
    -> VALUES(null,1,1),
    -> (null,1,2),
    -> (null,1,3),
    -> (null,2,1),
    -> (null,2,3);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

Column alias mysql query

• First query without using columns alias
• Second query use columns alias (USER_ID, NICK_NAME)

mysql> SELECT * FROM customer;
+-------------+--------+
| customer_id | name   |
+-------------+--------+
|           1 | Janny  |
|           2 | Bob    |
|           3 | Steven |
+-------------+--------+
3 rows in set (0.00 sec)

mysql> SELECT customer_id AS USER_ID, 
    -> name AS NICK_NAME
    -> FROM 
    -> customer;
+---------+-----------+
| USER_ID | NICK_NAME |
+---------+-----------+
|       1 | Janny     |
|       2 | Bob       |
|       3 | Steven    |
+---------+-----------+
3 rows in set (0.00 sec)

mysql select from table alias

• The first query without table alias
• Second query with table alias (tc)

mysql> SELECT * FROM item;
+---------+--------+-------+
| item_id | item   | price |
+---------+--------+-------+
|       1 | CD     |     5 |
|       2 | PHONE  |    45 |
|       3 | Laptop |   880 |
+---------+--------+-------+
3 rows in set (0.00 sec)

mysql> SELECT tc.* FROM item tc;
+---------+--------+-------+
| item_id | item   | price |
+---------+--------+-------+
|       1 | CD     |     5 |
|       2 | PHONE  |    45 |
|       3 | Laptop |   880 |
+---------+--------+-------+
3 rows in set (0.00 sec)

how to write subquery in mysql

Suquery can be used in the SELECT statement or the WHERE clause.

subquery in select statement

mysql> SELECT co.order_id, 
 -> co.customer_id AS USER_ID, 
 -> (SELECT c.name FROM customer c WHERE c.customer_id=co.customer_id) AS CUSTOMER_NAME, 
 -> co.item_id AS ITEM_NO, 
 -> (SELECT i.item FROM item i WHERE i.item_id = co.item_id) AS ITEM_NAME 
 -> FROM customer_order co;
+----------+---------+---------------+---------+-----------+
| order_id | USER_ID | CUSTOMER_NAME | ITEM_NO | ITEM_NAME |
+----------+---------+---------------+---------+-----------+
|        1 |       1 | Janny         |       1 | CD        |
|        2 |       1 | Janny         |       2 | PHONE     |
|        3 |       1 | Janny         |       3 | Laptop    |
|        4 |       2 | Bob           |       1 | CD        |
|        5 |       2 | Bob           |       3 | Laptop    |
+----------+---------+---------------+---------+-----------+
5 rows in set (0.00 sec)

subquery in where clause

Return only the item that customers have placed the order.

mysql> SELECT 
    -> it.*
    -> FROM
    ->  item it
    -> WHERE it.item_id 
    ->   IN
    ->  (SELECT co.item_id FROM customer_order co);
+---------+--------+-------+
| item_id | item   | price |
+---------+--------+-------+
|       1 | CD     |     5 |
|       2 | PHONE  |    45 |
|       3 | Laptop |   880 |
+---------+--------+-------+
3 rows in set (0.00 sec)