SQL: Table Aliases

 

When you are working with more than one table, you need a way to identify which table you are referring to. There are two ways to do this.

a.   Use the table name (ex: employee.emp_id)

b.   Using table alias name

 

Using table name

Let’s create two tables customer and customer_orders.

 

CREATE TABLE customer (

    customer_id int AUTO_INCREMENT,

    name varchar(20) NOT NULL,

    email_id varchar(20) NOT NULL,

    PRIMARY KEY(customer_id)

);

 

CREATE TABLE customer_orders (

    order_id int AUTO_INCREMENT,

    order_code varchar(20) NOT NULL,

    discount int NOT NULL,

    cust_id int,

    PRIMARY KEY(order_id),

    CONSTRAINT fk_fav_orders_cust_id FOREIGN KEY (cust_id) REFERENCES customer(customer_id)

);

 

Let’s insert some data to customer and customer_orders table.

 

INSERT INTO customer(name, email_id) VALUES ('Gopi', 'gopi@abcdef.com');

INSERT INTO customer(name, email_id) VALUES ('Jaideep', 'jaideep@abcdef.com');

INSERT INTO customer(name, email_id) VALUES ('Nisya', 'nisya@abcdef.com');

 

 

INSERT INTO customer_orders(order_code, discount, cust_id) VALUES ('LAP-123', 231, 3);

INSERT INTO customer_orders(order_code, discount, cust_id) VALUES ('OnePlu-9', 789, 1);

 

Let’s print data from tables.

mysql> SELECT * FROM customer;
+-------------+---------+--------------------+
| customer_id | name    | email_id           |
+-------------+---------+--------------------+
|           1 | Gopi    | gopi@abcdef.com    |
|           2 | Jaideep | jaideep@abcdef.com |
|           3 | Nisya   | nisya@abcdef.com   |
+-------------+---------+--------------------+
3 rows in set (0.00 sec)

mysql> 
mysql> SELECT * FROM customer_orders;
+----------+------------+----------+---------+
| order_id | order_code | discount | cust_id |
+----------+------------+----------+---------+
|        1 | LAP-123    |      231 |       3 |
|        2 | OnePlu-9   |      789 |       1 |
+----------+------------+----------+---------+
2 rows in set (0.00 sec)

 

Get all the customers and their orders data.

SELECT * FROM customer INNER JOIN customer_orders ON customer.customer_id=customer_orders.cust_id;

mysql> SELECT * FROM customer INNER JOIN customer_orders ON customer.customer_id=customer_orders.cust_id;
+-------------+-------+------------------+----------+------------+----------+---------+
| customer_id | name  | email_id         | order_id | order_code | discount | cust_id |
+-------------+-------+------------------+----------+------------+----------+---------+
|           3 | Nisya | nisya@abcdef.com |        1 | LAP-123    |      231 |       3 |
|           1 | Gopi  | gopi@abcdef.com  |        2 | OnePlu-9   |      789 |       1 |
+-------------+-------+------------------+----------+------------+----------+---------+
2 rows in set (0.00 sec)

  As you see the output, customer with id 2 details are not printed. It is because, customer with id 2 do not ordered any items till now. If you want to include all the customers (who ordered and who do not ordered), you can use LEFT JOIN.

 

mysql> SELECT * FROM customer LEFT JOIN customer_orders ON customer.customer_id=customer_orders.cust_id;
+-------------+---------+--------------------+----------+------------+----------+---------+
| customer_id | name    | email_id           | order_id | order_code | discount | cust_id |
+-------------+---------+--------------------+----------+------------+----------+---------+
|           1 | Gopi    | gopi@abcdef.com    |        2 | OnePlu-9   |      789 |       1 |
|           2 | Jaideep | jaideep@abcdef.com |     NULL | NULL       |     NULL |    NULL |
|           3 | Nisya   | nisya@abcdef.com   |        1 | LAP-123    |      231 |       3 |
+-------------+---------+--------------------+----------+------------+----------+---------+
3 rows in set (0.00 sec)

 

Using table aliases

SELECT * FROM customer c INNER JOIN customer_orders co ON c.customer_id=co.cust_id;

 

In the above query ‘c’ is an alias to the table customer and ‘co’ is an alias to customer_orders table.

mysql> SELECT * FROM customer c INNER JOIN customer_orders co ON c.customer_id=co.cust_id;
+-------------+-------+------------------+----------+------------+----------+---------+
| customer_id | name  | email_id         | order_id | order_code | discount | cust_id |
+-------------+-------+------------------+----------+------------+----------+---------+
|           3 | Nisya | nisya@abcdef.com |        1 | LAP-123    |      231 |       3 |
|           1 | Gopi  | gopi@abcdef.com  |        2 | OnePlu-9   |      789 |       1 |
+-------------+-------+------------------+----------+------------+----------+---------+
2 rows in set (0.00 sec)

 

 

 

Previous                                                    Next                                                    Home