Scribd
Upload
28 views16 pages

Joins

The document discusses the concept of joins in Database Management Systems (DBMS), which are operations that combine rows from two or more tables based on related columns to retrieve data. It explains various types of joins including Inner Join, Outer Join (Left, Right, Full), and provides SQL examples for each type. The document emphasizes the importance of joins for performing multi-table queries in relational databases.

Uploaded by

shubham31122005001
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
28 views16 pages

Joins

The document discusses the concept of joins in Database Management Systems (DBMS), which are operations that combine rows from two or more tables based on related columns to retrieve data. It explains various types of joins including Inner Join, Outer Join (Left, Right, Full), and provides SQL examples for each type. The document emphasizes the importance of joins for performing multi-table queries in relational databases.

Uploaded by

shubham31122005001
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
You are on page 1/ 16
Search... Aptitude Engineering Mathematics Discrete Mathematics Operating System DBMS Computer Netw Joins in DBMS Last Updated : 12 Apr, 2025 A join is an operation that combines the rows of two or more tables based on related columns. This operation is used for retrieving the data from multiple tables simultaneously using common columns of tables. In this article, we are going to discuss every point about joins. What is Join? Join is an operation in DBMS(Database Management System) that combines the rows of two or more tables based on related columns between them. The main purpose of join is to retrieve the data from multiple tables in other words Join is used to perform multi-table queries. It is denoted by pa. Syntax 1 R3 <- pa!) (®) where R1 and R2 are two relations to be joined and R3 is a relation that will hold the result of the join operation. Example Temp <- alStudert) 6 ue poy (2 where S and E are aliases of the student and exam respectively SQL JOIN Example cate a a Laer We use cookies to ensure you have the best browsing experience on our website. By Using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy ROLL_NO NAME ADDRESS PHONE Age a HARSH DELHI souccoccacr 18 2 PRATIK BIHAR soeoccnoan 19 3 RIYANKA siucuR! =| sccoencoce 20 4 DEEP RAMNAGAR | xx00Xxxxxx 15 5 SAPTARHI| KOLKATA — | s0aD0RD0RK 19 6 DHANRAJ | BARABAJAR | X0ODCOCOR 20 7 ROHIT BALURGHAT | ScOCCKKOON 18 8 NIRAJ ALIPUR XO 19 Table 1 - Student COURSE ID ROLL. NO is a. 2 2 2 3 3 4 1 5 4 9 5 10 4 uw Table 2 - StudentCourse Both these tables are connected by one common key (column) ie. ROLL_NO. We can perform a JOIN operation using the given SQL query: SELECT s.roll_no, s.name, s.address, s,phone, s.age, sc.course_id FROM Student s JOIN StudentCourse sc ON s.roll_no = sc.roll_no; We use cookies to ensure you have the best browsing experience on our website. By Using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy ROLL.LNO] NAME ADDRESS PHONE AGE COURSE_ID 1 HARSH DELHI XXKKKKXKKX 18. 1 2 PRATIK BIHAR XXXKKKXKKX 19. 2 3 PRIYANKA, SILIGURI XXXXXXXXXX 20 2 4 DEEP. RAMNAGAR — xxxxxxxxxx 18. 3 5 SAPTARHI KOLKATA = XXXXXXxxxx 19 1 Types of Join There are many types of Joins in SQL. Depending on the use case, you can use different types of SQL JOIN clauses. Here are the frequently used SQL. JOIN types: 1. Inner Join nner Join is a join operation in DBMS that combines two or more tables based on related columns and returns only rows that have matching values among tables. Inner join has two types. We use cookies to ensure you have the best browsing experience on our website, By Using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy Table A Table B Inner Join * Conditional join * Equi Join * Natural Join (a) Conditional Join Conditional join or Theta join is a type of inner join in which tables are combined based on the specified condition. # operators in In conditional join, the join condition can include <, >, <=, addition to the '=' operator. Example: Suppose two tables A and B Table A R Ss 10 5 ‘We use cookies to ensure you have the best browsing experience on our website. By you acknowledge that you have read and understood our Cookie Policy & Privacy Policy using our si Table B T OU 10 12 17, 6 ADM ser B Output R Ss T U 10 5 10 12 SQL Query SELECT R, S, T, U FROM Table A JOIN Table B ON S < T; Explanation: This query joins the table A, B and projects attributes R, S, T, U were the condition S < Tis satisfied. (b) Equi Join Equi Join is a type of inner join where the join condition uses the equality operator ('=') between columns. Example: Suppose there are two tables Table A and Table C We use cookies to ensure you have the best browsing experience on our website. By Using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy Column A Column B a a a b Table C Column A Column B A 4 A.column 8 = C.Column 8 (C) Output Column A Column B SQL Query SELECT * FROM Table A INNER JOIN Table C We use cookies to ensure you have the best browsing experience on our website. By Using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy Explanation: The data value "a" is available in both tables Hence we write that "a" is the table in the given output. (c) Natural Join Natural join is a type of inner join in which we do not need any comparison operators. In natural join, columns should have the same name and domain. There should be at least one common attribute between the two tables. Example: Suppose there are two tables Table A and Table B Table A Number Square 2 4 3 9 Table B Number Cube 2 8 3 27 AmB We use cookies to ensure you have the best browsing experience on our website, By Using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy Number Square Cube 2 4 8 3 9 27 SQL Query SELECT * FROM Table A NATURAL JOIN Table B; Explanation - Column Number is available in both tables Hence we write the "Number column once " after combining both tables. 2. Outer Join Outer join is a type of join that retrieves matching as well as non-matching records from related tables. There are three types of outer join * Left outer join * Right outer join * Full outer join (a) Left Outer Join It is also called _Left join. This type of outer join retrieves all records from the left table and retrieves matching records from the right table. Example: Suppose there are two tables Table A and Table B Table A Number Square We use cookies to ensure you have the best browsing experience on our website, By Using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy Number Square 3 9 4 16 Table B Number Cube 2 8 3 27 5 125 AnMB Output Number Square Cube 2 4 8 3 9 27 4 16 NULL We use cookies to ensure you have the best browsing experience on our website. By Using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy SELECT * FROM Table A LEFT OUTER JOIN Table B ON TableA.Number = TableB.Number; Explanation: Since we know in the left outer join we take all the columns from the left table (Here Table A) In the table A we can see that there is no Cube value for number 4. so we mark this as NULL. (b) Right Outer Join It is also called a right join. This type of outer join retrieves all records from the right table and retrieves matching records from the left table. And for the record which doesn't lies in Left table will be marked as NULL in result Set. Table A Fight Outer in Example: Suppose there are two tables Table A and Table B Arpt B Output: We use cookies to ensure you have the best browsing experience on our website. By Using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy Number Square Cube 2 4 8 3 9 27 5 NULL 125 SQL Query SELECT * FROM TableA RIGHT OUTER JOIN TableB ON TableA.Number= TableB.Number; Explanation: Since we know in the right outer join we take all the columns from the right table (Here Table B) In table A we can see that there is no square value for number 5. So we mark this as NULL. (c) Full Outer Join FULL JOIN creates the result set by combining the results of both LEFT JOIN and RIGHT JOIN. The result set will contain all the rows from both tables. For the rows for which there is no matching, the result set will contain NULL values. Example: Table A and Table B are the same as in the left outer join ADtB Output: We use cookies to ensure you have the best browsing experience on our website, By Using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy Number Square Cube 2 4 8 3 9 27 4 16 NULL 5 NULL 125 SQL Query SELECT * FROM Table A FULL OUTER JOIN Table B ON TableA.Number= TableB.Number; Explanation: Since we know in full outer join we take all the columns from both tables (Here Table A and Table B) In the table A and Table B we can see that there is no Cube value for number 4 and No Square value for 5 so we mark this as NULL. ( Comment | | Moreinfo [sree mins Next Article Join Dependencies in DBMS Similar Reads SQL Joins (Inner, Left, Right and Full Join) SQL joins are fundamental tools for combining data from multiple tables in relational databases. Joins allow efficient data retrieval, which is essential f... We use cookies to ensure you have the best browsing experience on our website. By Using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy Normal Forms in DBMS In the world of database management, Normal Forms are important for ensuring that data is structured logically, reducing redundancy, and... 15+ min read Introduction of Relational Algebra in DBMS Relational Algebra is a formal language used to query and manipulate relational databases, consisting of a set of operations like selection,... 15+ min read Join Dependencies in DBMS. Join Dependency (JD) can be illustrated as when the relation R is equal to the join of the sub-relations R1, R2,..., and Rn are present in the database. Join... 15+ min read SQL Natural Join Natural join is an SQL join operation that creates a join on the base of the common columns in the tables. To perform natural join there must be one 15+ min read Relational Model in DBMS The Relational Model represents data and their relationships through a collection of tables. Each table also known as a relation consists of rows an... 15+ min read SQL Inner Join SQL INNER JOIN is a powerful and frequently used operation in relational databases. It allows us to combine two or more tables based on a related... We use cookies to ensure you have the best browsing experience on our website. By Using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy A well-designed database aims to reduce unnecessary repetition of data. This is achieved through normalization which breaks data into smaller, mor... 15+ min read SQL Self Join A Self Join in SQL is a powerful technique that allows one to join a table with itself. This operation is helpful when you need to compare rows within the. 15+ min read Nested Loop Join in DBMS The joining of tables in relational databases is a common operation aimed at merging data from many different sources. In this article, we will look into... 15+ min read OG GegksforGeeks A-143, 7th Floor, Sovereign Corporate Tower, Sector- 136, Noida, Uttar Pradesh (201305) Registered Address: K 061, Tower k, Gulshan Vivante Apartment, Sector 137, Noida, Gautam. Buddh Nagar, Uttar Pradesh, 201305 Ss Os We use cookies to ensure you have the best browsing experience on our website. By Using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy About Us Legal Privacy Policy Careers InMedia Contact Us FG Corporate Solution Placement Training Program Languages Python Java cr PHP Gotang sol RLanguage Android Tutorial Data Science & ML Data Science With Python Beginner Data Sclenet Machine Learning MLMaths Data Visualisation Pandas NumPy NLP Deep Learning. Python Tutorial Python Programming Examples Django Tutorial Python Projects Python Tkinter Web Scraping Opency Tutorial Python Interview Question Devops Git ws Job-A-Thon Hiring Challenge GIG Weekly Contest Offline Classroom Program DSAin JAVA/CH Master System Design Master CP. GeeksforGeeks Videos DSA Data Structures Algorithms. DSA for Beginners Basic DSA Problems DSA Roadmap DSA interview Questions Competitive Programming Web Technologies HTML ess JavaScript ‘TypeScri Reacts Nexus Nodels Bootstrap Tailwind CSS Computer Science GATE CSNotes Operating Systems Computer Network Database Management System Software Engineering Digital Logic Design Engineering Maths system Design High Level Design Low Level Design ‘We use cookies to ensure you have the best browsing experience on our website. By Using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy Devops Roadmap ‘School Subjects Mathematics Physics Chemistry Biology Social Science English Grammar Preparation Corner ‘Company-Wise Recruitment Process Aptitude Preparation Puzzles Company-Wise Preparation Machine Learning/Data Science Complete Machine Learning & Data Science Program -[UIVE] Data Analytics Training using Excel, SQL, Python & PowerBl- (uve Data Science Training Program - [LIVE Data Science Course with IBM Certification Clouds/Devops DevOps Engineering ‘AWS Solutions Architect Certification Salesforce Certified Administrator Course System Design Bootcamp Interview Questions Databases sat MySQL PostgreSQL PL/SQL. MongooB More Tutorials Software Development Software Testing Product Management Project Management Linux Excel All Cheat Sheets Programming Languages Programming with Data Structures C+ Programming Course Java Programming Course Python Full Course GATE 2026 GATE CS Rank Booster GATE DA Rank Booster GATE CS & IT Course 2026 GATE DA Course 2026 GATE Rank Predictor @GeeksforGeeks, Sanchhaya Education Private Limited, All rights reserved ‘We use cookies to ensure you have the best browsing experience on our website. By Using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy

You might also like