The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records from the left table (table1). The result is 0 records from the left side, if there is no match.
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
---------
CREATE TABLE Emp (
emp_id int(10) NOT NULL,
name varchar(40) NOT NULL,
birthdate date NOT NULL,
gender varchar(10) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_id)
);
INSERT INTO Emp (emp_id, name, birthdate, gender, hire_date) VALUES
(101, 'Bryan', '1988-08-12', 'M', '2015-08-26'),
(102, 'Joseph', '1978-05-12', 'M', '2014-10-21'),
(103, 'Mike', '1984-10-13', 'M', '2017-10-28'),
(104, 'Daren', '1979-04-11', 'M', '2006-11-01'),
(105, 'Marie', '1990-02-11', 'F', '2018-10-12');
------------------
CREATE TABLE Pay (
payment_id int(10) PRIMARY KEY NOT NULL,
emp_id int(10) NOT NULL,
amount float NOT NULL,
payment_date date NOT NULL
);
INSERT INTO Pay (payment_id, emp_id, amount, payment_date) VALUES
(401, 106, 1200, '2015-09-15'),
(402, 105, 1200, '2015-09-30'),
(403, 109, 1500, '2015-10-15'),
(404, 110, 1500, '2015-10-30'),
(405, 144, 1800, '2015-09-15'),
(406, 102, 1800, '2015-09-30');
select first_name,amount from customers
left join orders
on customers.cust_id=orders.customer_id
;
select * from customers
right join orders
on customers.cust_id=orders.customer_id
;
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
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> select ram;
ERROR 1054 (42S22): Unknown column 'ram' in 'field list'
mysql> show databases
-> ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| ram |
| sakila |
| sys |
| world |
+--------------------+
7 rows in set (0.01 sec)
mysql> use ram;
Database changed
mysql> show tables;
+---------------+
| Tables_in_ram |
+---------------+
| customers |
| orders |
+---------------+
2 rows in set (0.00 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 * from customers
-> left join orders
-> on customers.cust_id=orders.customer_id
-> ;
+---------+------------+-----------+------------------------------+------+------------+--------+-------------+
| cust_id | first_name | last_name | email | id | order_date | amount | customer_id |
+---------+------------+-----------+------------------------------+------+------------+--------+-------------+
| 300 | venkat | vlr | venkat.vlrtraining@gmail.com | 12 | 2021-05-10 | 199.99 | 300 |
| 301 | praveen | g | g.praveen@gmail.com | 13 | 2021-09-11 | 321.50 | 301 |
| 301 | praveen | g | g.praveen@gmail.com | 18 | 2021-02-11 | 31.50 | 301 |
| 302 | lakshman | k | lakshman.k@gmail.com | NULL | NULL | NULL | NULL |
| 303 | Naveen | d | Naveend@gmail.com | 14 | 2021-06-12 | 789.67 | 303 |
| 303 | Naveen | d | Naveend@gmail.com | 19 | 2021-06-12 | 79.67 | 303 |
| 304 | ambani | e | e.ambani@aol.com | 15 | 2021-01-03 | 102.50 | 304 |
| 304 | ambani | e | e.ambani@aol.com | 20 | 2021-08-03 | 102.50 | 304 |
| 305 | saritha | g | saritha@gmail.com | 16 | 2021-04-11 | 850.25 | 305 |
| 305 | saritha | g | saritha@gmail.com | 21 | 2021-07-11 | 80.25 | 305 |
| 500 | harika | p | harika.p@aol.com | 17 | 2021-03-13 | 689.25 | 500 |
| 500 | harika | p | harika.p@aol.com | 22 | 2021-12-13 | 669.25 | 500 |
+---------+------------+-----------+------------------------------+------+------------+--------+-------------+
12 rows in set (0.00 sec)
mysql> select * from customers
-> right join orders
-> on customers.cust_id=orders.customer_id
-> ;
+---------+------------+-----------+------------------------------+----+------------+--------+-------------+
| cust_id | first_name | last_name | email | id | order_date | amount | customer_id |
+---------+------------+-----------+------------------------------+----+------------+--------+-------------+
| 300 | venkat | vlr | venkat.vlrtraining@gmail.com | 12 | 2021-05-10 | 199.99 | 300 |
| 301 | praveen | g | g.praveen@gmail.com | 13 | 2021-09-11 | 321.50 | 301 |
| 303 | Naveen | d | Naveend@gmail.com | 14 | 2021-06-12 | 789.67 | 303 |
| 304 | ambani | e | e.ambani@aol.com | 15 | 2021-01-03 | 102.50 | 304 |
| 305 | saritha | g | saritha@gmail.com | 16 | 2021-04-11 | 850.25 | 305 |
| 500 | harika | p | harika.p@aol.com | 17 | 2021-03-13 | 689.25 | 500 |
| 301 | praveen | g | g.praveen@gmail.com | 18 | 2021-02-11 | 31.50 | 301 |
| 303 | Naveen | d | Naveend@gmail.com | 19 | 2021-06-12 | 79.67 | 303 |
| 304 | ambani | e | e.ambani@aol.com | 20 | 2021-08-03 | 102.50 | 304 |
| 305 | saritha | g | saritha@gmail.com | 21 | 2021-07-11 | 80.25 | 305 |
| 500 | harika | p | harika.p@aol.com | 22 | 2021-12-13 | 669.25 | 500 |
+---------+------------+-----------+------------------------------+----+------------+--------+-------------+
11 rows in set (0.00 sec)
mysql>
mysql> CREATE TABLE Emp (
-> emp_id int(10) NOT NULL,
-> name varchar(40) NOT NULL,
-> birthdate date NOT NULL,
-> gender varchar(10) NOT NULL,
-> hire_date date NOT NULL,
-> PRIMARY KEY (emp_id)
-> );
Query OK, 0 rows affected, 1 warning (2.57 sec)
mysql>
mysql> INSERT INTO Emp (emp_id, name, birthdate, gender, hire_date) VALUES
-> (101, 'Bryan', '1988-08-12', 'M', '2015-08-26'),
-> (102, 'Joseph', '1978-05-12', 'M', '2014-10-21'),
-> (103, 'Mike', '1984-10-13', 'M', '2017-10-28'),
-> (104, 'Daren', '1979-04-11', 'M', '2006-11-01'),
-> (105, 'Marie', '1990-02-11', 'F', '2018-10-12');
Query OK, 5 rows affected (0.29 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> CREATE TABLE Pay (
-> payment_id int(10) PRIMARY KEY NOT NULL,
-> emp_id int(10) NOT NULL,
-> amount float NOT NULL,
-> payment_date date NOT NULL,
->
-> );
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 7
mysql>
mysql>
mysql> INSERT INTO Pay (payment_id, emp_id, amount, payment_date) VALUES
-> (301, 101, 1200, '2015-09-15'),
-> (302, 101, 1200, '2015-09-30'),
-> (303, 101, 1500, '2015-10-15'),
-> (304, 101, 1500, '2015-10-30'),
-> (305, 102, 1800, '2015-09-15'),
-> (306, 102, 1800, '2015-09-30')
->
-> ;
ERROR 1146 (42S02): Table 'ram.pay' doesn't exist
mysql> CREATE TABLE Pay (
-> payment_id int(10) PRIMARY KEY NOT NULL,
-> emp_id int(10) NOT NULL,
-> amount float NOT NULL,
-> payment_date date NOT NULL,
->
-> );
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 7
mysql> CREATE TABLE Pay (
-> payment_id int(10) PRIMARY KEY NOT NULL,
-> emp_id int(10) NOT NULL,
-> amount float NOT NULL,
-> payment_date date NOT NULL
->
-> );
Query OK, 0 rows affected, 2 warnings (1.14 sec)
mysql>
mysql> INSERT INTO Pay (payment_id, emp_id, amount, payment_date) VALUES
-> (301, 101, 1200, '2015-09-15'),
-> (302, 101, 1200, '2015-09-30'),
-> (303, 101, 1500, '2015-10-15'),
-> (304, 101, 1500, '2015-10-30'),
-> (305, 102, 1800, '2015-09-15'),
-> (306, 102, 1800, '2015-09-30');
Query OK, 6 rows affected (0.17 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from emp
-> ;
+--------+--------+------------+--------+------------+
| emp_id | name | birthdate | gender | hire_date |
+--------+--------+------------+--------+------------+
| 101 | Bryan | 1988-08-12 | M | 2015-08-26 |
| 102 | Joseph | 1978-05-12 | M | 2014-10-21 |
| 103 | Mike | 1984-10-13 | M | 2017-10-28 |
| 104 | Daren | 1979-04-11 | M | 2006-11-01 |
| 105 | Marie | 1990-02-11 | F | 2018-10-12 |
+--------+--------+------------+--------+------------+
5 rows in set (0.02 sec)
mysql> select * from pay;
+------------+--------+--------+--------------+
| payment_id | emp_id | amount | payment_date |
+------------+--------+--------+--------------+
| 301 | 101 | 1200 | 2015-09-15 |
| 302 | 101 | 1200 | 2015-09-30 |
| 303 | 101 | 1500 | 2015-10-15 |
| 304 | 101 | 1500 | 2015-10-30 |
| 305 | 102 | 1800 | 2015-09-15 |
| 306 | 102 | 1800 | 2015-09-30 |
+------------+--------+--------+--------------+
6 rows in set (0.00 sec)
mysql> select * from emp left join pay on
-> emp.emp_id = pay.emp_id;
+--------+--------+------------+--------+------------+------------+--------+--------+--------------+
| emp_id | name | birthdate | gender | hire_date | payment_id | emp_id | amount | payment_date |
+--------+--------+------------+--------+------------+------------+--------+--------+--------------+
| 101 | Bryan | 1988-08-12 | M | 2015-08-26 | 304 | 101 | 1500 | 2015-10-30 |
| 101 | Bryan | 1988-08-12 | M | 2015-08-26 | 303 | 101 | 1500 | 2015-10-15 |
| 101 | Bryan | 1988-08-12 | M | 2015-08-26 | 302 | 101 | 1200 | 2015-09-30 |
| 101 | Bryan | 1988-08-12 | M | 2015-08-26 | 301 | 101 | 1200 | 2015-09-15 |
| 102 | Joseph | 1978-05-12 | M | 2014-10-21 | 306 | 102 | 1800 | 2015-09-30 |
| 102 | Joseph | 1978-05-12 | M | 2014-10-21 | 305 | 102 | 1800 | 2015-09-15 |
| 103 | Mike | 1984-10-13 | M | 2017-10-28 | NULL | NULL | NULL | NULL |
| 104 | Daren | 1979-04-11 | M | 2006-11-01 | NULL | NULL | NULL | NULL |
| 105 | Marie | 1990-02-11 | F | 2018-10-12 | NULL | NULL | NULL | NULL |
+--------+--------+------------+--------+------------+------------+--------+--------+--------------+
9 rows in set (0.00 sec)
mysql> select * from emp right join pay on
-> emp.emp_id = pay.emp_id;
+--------+--------+------------+--------+------------+------------+--------+--------+--------------+
| emp_id | name | birthdate | gender | hire_date | payment_id | emp_id | amount | payment_date |
+--------+--------+------------+--------+------------+------------+--------+--------+--------------+
| 101 | Bryan | 1988-08-12 | M | 2015-08-26 | 301 | 101 | 1200 | 2015-09-15 |
| 101 | Bryan | 1988-08-12 | M | 2015-08-26 | 302 | 101 | 1200 | 2015-09-30 |
| 101 | Bryan | 1988-08-12 | M | 2015-08-26 | 303 | 101 | 1500 | 2015-10-15 |
| 101 | Bryan | 1988-08-12 | M | 2015-08-26 | 304 | 101 | 1500 | 2015-10-30 |
| 102 | Joseph | 1978-05-12 | M | 2014-10-21 | 305 | 102 | 1800 | 2015-09-15 |
| 102 | Joseph | 1978-05-12 | M | 2014-10-21 | 306 | 102 | 1800 | 2015-09-30 |
+--------+--------+------------+--------+------------+------------+--------+--------+--------------+
6 rows in set (0.00 sec)
mysql> INSERT INTO Pay (payment_id, emp_id, amount, payment_date) VALUES
-> (401, 106, 1200, '2015-09-15'),
-> (402, 105, 1200, '2015-09-30'),
-> (403, 109, 1500, '2015-10-15'),
-> (404, 110, 1500, '2015-10-30'),
-> (405, 144, 1800, '2015-09-15'),
-> (406, 102, 1800, '2015-09-30');
Query OK, 6 rows affected (0.11 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from emp right join pay on
-> emp.emp_id = pay.emp_id;
+--------+--------+------------+--------+------------+------------+--------+--------+--------------+
| emp_id | name | birthdate | gender | hire_date | payment_id | emp_id | amount | payment_date |
+--------+--------+------------+--------+------------+------------+--------+--------+--------------+
| 101 | Bryan | 1988-08-12 | M | 2015-08-26 | 301 | 101 | 1200 | 2015-09-15 |
| 101 | Bryan | 1988-08-12 | M | 2015-08-26 | 302 | 101 | 1200 | 2015-09-30 |
| 101 | Bryan | 1988-08-12 | M | 2015-08-26 | 303 | 101 | 1500 | 2015-10-15 |
| 101 | Bryan | 1988-08-12 | M | 2015-08-26 | 304 | 101 | 1500 | 2015-10-30 |
| 102 | Joseph | 1978-05-12 | M | 2014-10-21 | 305 | 102 | 1800 | 2015-09-15 |
| 102 | Joseph | 1978-05-12 | M | 2014-10-21 | 306 | 102 | 1800 | 2015-09-30 |
| NULL | NULL | NULL | NULL | NULL | 401 | 106 | 1200 | 2015-09-15 |
| 105 | Marie | 1990-02-11 | F | 2018-10-12 | 402 | 105 | 1200 | 2015-09-30 |
| NULL | NULL | NULL | NULL | NULL | 403 | 109 | 1500 | 2015-10-15 |
| NULL | NULL | NULL | NULL | NULL | 404 | 110 | 1500 | 2015-10-30 |
| NULL | NULL | NULL | NULL | NULL | 405 | 144 | 1800 | 2015-09-15 |
| 102 | Joseph | 1978-05-12 | M | 2014-10-21 | 406 | 102 | 1800 | 2015-09-30 |
+--------+--------+------------+--------+------------+------------+--------+--------+--------------+
12 rows in set (0.02 sec)
mysql> INSERT INTO Pay (payment_id, emp_id, amount, payment_date) VALUES
-> (401, 106, 1200, '2015-09-15'),
-> (402, 105, 1200, '2015-09-30'),
-> (403, 109, 1500, '2015-10-15'),
-> (404, 110, 1500, '2015-10-30'),
-> (405, 144, 1800, '2015-09-15'),
-> (406, 102, 1800, '2015-09-30')werwer
-> ;
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 'werwer' at line 7
mysql>
--------------------
---------------------
------------------
CREATE TABLE Emp (
emp_id int(10) NOT NULL,
name varchar(40) NOT NULL,
birthdate date NOT NULL,
gender varchar(10) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_id)
);
INSERT INTO Emp (emp_id, name, birthdate, gender, hire_date) VALUES
(101, 'Bryan', '1988-08-12', 'M', '2015-08-26'),
(102, 'Joseph', '1978-05-12', 'M', '2014-10-21'),
(103, 'Mike', '1984-10-13', 'M', '2017-10-28'),
(104, 'Daren', '1979-04-11', 'M', '2006-11-01'),
(105, 'Marie', '1990-02-11', 'F', '2018-10-12');
------------------
CREATE TABLE Pay (
payment_id int(10) PRIMARY KEY NOT NULL,
emp_id int(10) NOT NULL,
amount float NOT NULL,
payment_date date NOT NULL,
FOREIGN KEY (emp_id) REFERENCES Employee (emp_id) ON DELETE CASCADE
);
INSERT INTO Pay (payment_id, emp_id, amount, payment_date) VALUES
(301, 101, 1200, '2015-09-15'),
(302, 101, 1200, '2015-09-30'),
(303, 101, 1500, '2015-10-15'),
(304, 101, 1500, '2015-10-30'),
(305, 102, 1800, '2015-09-15'),
(306, 102, 1800, '2015-09-30')
------------------
ON DELETE CASCADE
DELETE FROM Emp WHERE emp_id = 102;
No comments:
Post a Comment
Note: only a member of this blog may post a comment.