SQL LEFT JOIN Keyword
The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side, if there is no match.
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name
select first_name,cust_id,sum(amount) from customers
left join orders
on customers.cust_id=orders.customer_id
group by cust_id;
----------------
select first_name,cust_id,ifnull(sum(amount),0) from customers
left join orders
on customers.cust_id=orders.customer_id
group by cust_id;
-----------
select first_name,cust_id,ifnull(sum(amount),0) as total
from customers
left join orders
on customers.cust_id=orders.customer_id
group by cust_id
order by total;
-----------
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.25 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases
-> ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| ram |
| sakila |
| sys |
| world |
+--------------------+
7 rows in set (0.38 sec)
mysql> use ram
Database changed
mysql> show tables;
+---------------+
| Tables_in_ram |
+---------------+
| customers |
| orders |
+---------------+
2 rows in set (0.11 sec)
mysql> select * from customers;
+---------+------------+-----------+------------------------------+
| cust_id | first_name | last_name | email |
+---------+------------+-----------+------------------------------+
| 300 | venkat | vlr | venkat.vlrtraining@gmail.com |
| 301 | praveen | g | g.praveen@gmail.com |
| 302 | lakshman | k | lakshman.k@gmail.com |
| 303 | Naveen | d | Naveend@gmail.com |
| 304 | ambani | e | e.ambani@aol.com |
| 305 | saritha | g | saritha@gmail.com |
| 500 | harika | p | harika.p@aol.com |
+---------+------------+-----------+------------------------------+
7 rows in set (0.12 sec)
mysql> select * from orders;
+----+------------+--------+-------------+
| id | order_date | amount | customer_id |
+----+------------+--------+-------------+
| 12 | 2021-05-10 | 199.99 | 300 |
| 13 | 2021-09-11 | 321.50 | 301 |
| 14 | 2021-06-12 | 789.67 | 303 |
| 15 | 2021-01-03 | 102.50 | 304 |
| 16 | 2021-04-11 | 850.25 | 305 |
| 17 | 2021-03-13 | 689.25 | 500 |
| 18 | 2021-02-11 | 31.50 | 301 |
| 19 | 2021-06-12 | 79.67 | 303 |
| 20 | 2021-08-03 | 102.50 | 304 |
| 21 | 2021-07-11 | 80.25 | 305 |
| 22 | 2021-12-13 | 669.25 | 500 |
+----+------------+--------+-------------+
11 rows in set (0.04 sec)
mysql> select first_name,amount from customers
-> left join orders
-> on customers.cust_id=orders.customer_id:
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':' at line 3
mysql> select first_name,amount from customers
-> left join orders
-> on customers.cust_id=orders.customer_id;
+------------+--------+
| first_name | amount |
+------------+--------+
| venkat | 199.99 |
| praveen | 321.50 |
| praveen | 31.50 |
| lakshman | NULL |
| Naveen | 789.67 |
| Naveen | 79.67 |
| ambani | 102.50 |
| ambani | 102.50 |
| saritha | 850.25 |
| saritha | 80.25 |
| harika | 689.25 |
| harika | 669.25 |
+------------+--------+
12 rows in set (0.04 sec)
mysql> select first_name,amount from customers
-> left join orders
-> on customers.cust_id=orders.customer_id;
+------------+--------+
| first_name | amount |
+------------+--------+
| venkat | 199.99 |
| praveen | 321.50 |
| praveen | 31.50 |
| lakshman | NULL |
| Naveen | 789.67 |
| Naveen | 79.67 |
| ambani | 102.50 |
| ambani | 102.50 |
| saritha | 850.25 |
| saritha | 80.25 |
| harika | 689.25 |
| harika | 669.25 |
+------------+--------+
12 rows in set (0.00 sec)
mysql> select first_name,customer_id,amount from customers
-> left join orders
-> on customers.cust_id=orders.customer_id;
+------------+-------------+--------+
| first_name | customer_id | amount |
+------------+-------------+--------+
| venkat | 300 | 199.99 |
| praveen | 301 | 321.50 |
| praveen | 301 | 31.50 |
| lakshman | NULL | NULL |
| Naveen | 303 | 789.67 |
| Naveen | 303 | 79.67 |
| ambani | 304 | 102.50 |
| ambani | 304 | 102.50 |
| saritha | 305 | 850.25 |
| saritha | 305 | 80.25 |
| harika | 500 | 689.25 |
| harika | 500 | 669.25 |
+------------+-------------+--------+
12 rows in set (0.00 sec)
mysql> select first_name,cust_id,amount from customers
-> left join orders
-> on customers.cust_id=orders.customer_id;
+------------+---------+--------+
| first_name | cust_id | amount |
+------------+---------+--------+
| venkat | 300 | 199.99 |
| praveen | 301 | 321.50 |
| praveen | 301 | 31.50 |
| lakshman | 302 | NULL |
| Naveen | 303 | 789.67 |
| Naveen | 303 | 79.67 |
| ambani | 304 | 102.50 |
| ambani | 304 | 102.50 |
| saritha | 305 | 850.25 |
| saritha | 305 | 80.25 |
| harika | 500 | 689.25 |
| harika | 500 | 669.25 |
+------------+---------+--------+
12 rows in set (0.00 sec)
mysql>
mysql> select first_name,cust_id,sum(amount) from customers
-> left join orders
-> on customers.cust_id=orders.customer_id;
+------------+---------+-------------+
| first_name | cust_id | sum(amount) |
+------------+---------+-------------+
| venkat | 300 | 3916.33 |
+------------+---------+-------------+
1 row in set (0.04 sec)
mysql> select first_name,cust_id,sum(amount) from customers
-> left join orders
-> on customers.cust_id=orders.customer_id
-> group by cust_id;
+------------+---------+-------------+
| first_name | cust_id | sum(amount) |
+------------+---------+-------------+
| venkat | 300 | 199.99 |
| praveen | 301 | 353.00 |
| lakshman | 302 | NULL |
| Naveen | 303 | 869.34 |
| ambani | 304 | 205.00 |
| saritha | 305 | 930.50 |
| harika | 500 | 1358.50 |
+------------+---------+-------------+
7 rows in set (0.00 sec)
mysql> select first_name,cust_id,ifnull(sum(amount),0) from customers
-> left join orders
-> on customers.cust_id=orders.customer_id
-> group by cust_id;
+------------+---------+-----------------------+
| first_name | cust_id | ifnull(sum(amount),0) |
+------------+---------+-----------------------+
| venkat | 300 | 199.99 |
| praveen | 301 | 353.00 |
| lakshman | 302 | 0.00 |
| Naveen | 303 | 869.34 |
| ambani | 304 | 205.00 |
| saritha | 305 | 930.50 |
| harika | 500 | 1358.50 |
+------------+---------+-----------------------+
7 rows in set (0.02 sec)
mysql> select first_name,cust_id,ifnull(sum(amount),0) as total
-> from customers
-> left join orders
-> on customers.cust_id=orders.customer_id
-> group by cust_id;
+------------+---------+---------+
| first_name | cust_id | total |
+------------+---------+---------+
| venkat | 300 | 199.99 |
| praveen | 301 | 353.00 |
| lakshman | 302 | 0.00 |
| Naveen | 303 | 869.34 |
| ambani | 304 | 205.00 |
| saritha | 305 | 930.50 |
| harika | 500 | 1358.50 |
+------------+---------+---------+
7 rows in set (0.00 sec)
mysql> select first_name,cust_id,ifnull(sum(amount),0) as total
-> from customers
-> left join orders
-> on customers.cust_id=orders.customer_id
-> group by cust_id
-> orderby total;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'orderby total' at line 6
mysql> select first_name,cust_id,ifnull(sum(amount),0) as total
-> from customers
-> left join orders
-> on customers.cust_id=orders.customer_id
-> group by cust_id
-> order by total;
+------------+---------+---------+
| first_name | cust_id | total |
+------------+---------+---------+
| lakshman | 302 | 0.00 |
| venkat | 300 | 199.99 |
| ambani | 304 | 205.00 |
| praveen | 301 | 353.00 |
| Naveen | 303 | 869.34 |
| saritha | 305 | 930.50 |
| harika | 500 | 1358.50 |
+------------+---------+---------+
7 rows in set (0.03 sec)
mysql>
No comments:
Post a Comment
Note: only a member of this blog may post a comment.