Online Courses
Free Tutorials  Go to Your University  Placement Preparation 
0 like 0 dislike
1.3k views
in RTU/BTU B.Tech (CSE-IV Sem) DBMS Lab by Goeduhub's Expert (7.6k points)
Write the queries to implement the joins i.e outer joins , inner joins ,left join, right joins.

Goeduhub's Top Online Courses @Udemy

For Indian Students- INR 360/- || For International Students- $9.99/-

S.No.

Course Name

 Coupon

1.

Tensorflow 2 & Keras:Deep Learning & Artificial Intelligence || Labeled as Highest Rated Course by Udemy

Apply Coupon

2.

Complete Machine Learning & Data Science with Python| ML A-Z Apply Coupon

3.

Complete Python Programming from scratch | Python Projects Apply Coupon
    More Courses

1 Answer

0 like 0 dislike
by Goeduhub's Expert (7.6k points)
edited by
 
Best answer

SQL JOINS

JOINS :  join is used to combine columns from one (self-join) or more tables based on the values of the common columns between the tables. A JOIN is a means for combining fields from two tables by using values common to each.There are four types of joins :-

  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL JOIN

sample tables :Suppose we have two tables called basket_a and basket_b that stores fruits:

CREATE TABLE basket_a (id INT PRIMARY KEY, fruit VARCHAR (100) NOT NUL); 

INSERT INTO basket_a (id, fruit)VALUES(1, 'Apple'),

INSERT INTO basket_a (id, fruit)VALUES (2, 'Orange'),

INSERT INTO basket_a (id, fruit)VALUES(3, 'Banana'),

INSERT INTO basket_a (id, fruit)VALUES (4, 'Cucumber');

CREATE TABLE basket_b (id INT PRIMARY KEY,fruit VARCHAR (100) NOT NUL);

INSERT INTO basket_b (id, fruit)VALUES(1, 'Orange'),

INSERT INTO basket_b (id, fruit)VALUES(2, 'Apple'),

INSERT INTO basket_b (id, fruit)VALUES(3, 'Watermelon'),

INSERT INTO basket_b (id, fruit)VALUES(4, 'Pear');

INNER JOIN : This type of join returns those records which have matching values in both tables.

SELECT a.id id_a, a.fruit fruit_a, b.id id_b, b.fruit fruit_b FROM basket_a a INNER JOIN basket_b b ON a.fruit = b.fruit;

Output : 

id_afruit_aid_bfruit_b
1apple  2apple
2orange1orange

LEFT JOIN :  returns all rows from the left table, even if there are no matches in the right table.

SELECT a.id id_a, a.fruit fruit_a, b.id id_b, b.fruit fruit_b FROM  basket_a a LEFT JOIN basket_b b ON a.fruit = b.fruit;

Output : 

id_afruit_aid_bfruit_b
1apple2apple
2orange1orange
3banananullnull
4cucumbernullnull

RIGHT JOIN : returns all rows from the right table, even if there are no matches in the left table.

SELECT a.id id_a, a.fruit fruit_a,  b.id id_b,  b.fruit fruit_b FROM basket_a a RIGHT JOIN basket_b b ON a.fruit = b.fruit;

Output : 

id_afruit_aid_bfruit_b
2orange1orange
1apple2apple
nullnull3watermelon
nullnull4pear

FULL JOIN: returns rows when there is a match in one of the tables.

SELECT a.id id_a, a.fruit fruit_a, b.id id_b, b.fruit fruit_b FROM basket_a a FULL JOIN basket_b b ON a.fruit = b.fruit WHERE a.id IS NULL OR b.id IS NULL;

Output : 

id_afruit_aid_bfruit_b
3banananullnull
4cucumbernullnull
nullnull3watermelon
nullnull4pear


For more Manipal University Jaipur CSE-IV Sem DBMS Lab Experiments CLICK HERE

3.3k questions

7.1k answers

394 comments

4.6k users

 Goeduhub:

About Us | Contact Us || Terms & Conditions | Privacy Policy || Youtube Channel || Telegram Channel © goeduhub.com Social::   |  | 
...