Monday, 11 October 2021

left Join in sql sql videos in telugu 65

 https://youtu.be/peZ8si1Is5E

-------------------------------------------------
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       | [email protected] |   12 | 2021-05-10 | 199.99 |         300 |
|     301 | praveen    | g         | [email protected]          |   13 | 2021-09-11 | 321.50 |         301 |
|     301 | praveen    | g         | [email protected]          |   18 | 2021-02-11 |  31.50 |         301 |
|     302 | lakshman   | k         | [email protected]         | NULL | NULL       |   NULL |        NULL |
|     303 | Naveen     | d         | [email protected]            |   14 | 2021-06-12 | 789.67 |         303 |
|     303 | Naveen     | d         | [email protected]            |   19 | 2021-06-12 |  79.67 |         303 |
|     304 | ambani     | e         | [email protected]             |   15 | 2021-01-03 | 102.50 |         304 |
|     304 | ambani     | e         | [email protected]             |   20 | 2021-08-03 | 102.50 |         304 |
|     305 | saritha    | g         | [email protected]com            |   16 | 2021-04-11 | 850.25 |         305 |
|     305 | saritha    | g         | [email protected]            |   21 | 2021-07-11 |  80.25 |         305 |
|     500 | harika     | p         | [email protected]             |   17 | 2021-03-13 | 689.25 |         500 |
|     500 | harika     | p         | [email protected]             |   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       | [email protected] | 12 | 2021-05-10 | 199.99 |         300 |
|     301 | praveen    | g         | [email protected]          | 13 | 2021-09-11 | 321.50 |         301 |
|     303 | Naveen     | d         | [email protected]            | 14 | 2021-06-12 | 789.67 |         303 |
|     304 | ambani     | e         | [email protected]             | 15 | 2021-01-03 | 102.50 |         304 |
|     305 | saritha    | g         | [email protected]            | 16 | 2021-04-11 | 850.25 |         305 |
|     500 | harika     | p         | [email protected]             | 17 | 2021-03-13 | 689.25 |         500 |
|     301 | praveen    | g         | [email protected]          | 18 | 2021-02-11 |  31.50 |         301 |
|     303 | Naveen     | d         | [email protected]            | 19 | 2021-06-12 |  79.67 |         303 |
|     304 | ambani     | e         | [email protected]             | 20 | 2021-08-03 | 102.50 |         304 |
|     305 | saritha    | g         | [email protected]            | 21 | 2021-07-11 |  80.25 |         305 |
|     500 | harika     | p         | [email protected]             | 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.