Scribd
Upload
63 views7 pages

SQL Join Types and Examples

The document discusses different types of SQL joins including inner, outer, full and natural joins. It provides the syntax and examples for each join type. The document also includes a practical assignment on dropping and creating tables and inserting sample data.

Uploaded by

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

SQL Join Types and Examples

The document discusses different types of SQL joins including inner, outer, full and natural joins. It provides the syntax and examples for each join type. The document also includes a practical assignment on dropping and creating tables and inserting sample data.

Uploaded by

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

Experiment No.

3
Environment: Microsoft Windows
Tools/ Language: Oracle/SQL

Objective: Write the SQL queries using Set Operations and Joins.

Theory & Concepts:

SQL JOINS are used to retrieve data from multiple tables. A SQL JOIN is performed
whenever two or more tables are joined in a SQL statement.

There are different types of SQL joins:

SQL INNER JOIN (or sometimes called simple join)


SQL CROSS JOIN
SQL NATURAL JOIN
SQL LEFT OUTER JOIN (or sometimes called LEFT JOIN)
SQL RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)
SQL FULL OUTER JOIN (or sometimes called FULL JOIN)

SQL INNER JOIN (SIMPLE JOIN)


SQL INNER JOINS return all rows from multiple tables where the join condition is met.

Syntax
The syntax for the SQL INNER JOIN is:

SELECT columns
FROM table1
INNER JOIN table2
ON [Link] = [Link];

If the tables COUNTRIES and CITIES have two common columns named
POPULATION and COUNTRY_ISO_CODE, JOIN applies equality condition on ISO
codes with cities having less POPULATION attributes:

SELECT * FROM
COUNTRIES
INNER JOIN CITIES
On COUNTRIES.COUNTRY_ISO_CODE = CITIES.COUNTRY_ISO_CODE
And [Link] > [Link];

SQL LEFT OUTER JOIN


Another type of join is called a LEFT OUTER JOIN. This type of join returns all rows
from the LEFT-hand table specified in the ON condition and only those rows from the
other table where the joined fields are equal (join condition is met).

Syntax

The syntax for the SQL LEFT OUTER JOIN is:


SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON [Link] = [Link];

In some databases, the LEFT OUTER JOIN keywords are replaced with LEFT JOIN.
SELECT * FROM
COUNTRIES
LEFT JOIN CITIES
On COUNTRIES.COUNTRY_ISO_CODE=CITIES. COUNTRY_ISO_CODE
And [Link] >[Link];

SQL RIGHT OUTER JOIN


Another type of join is called a SQL RIGHT OUTER JOIN. This type of join returns all
rows from the RIGHT-hand table specified in the ON condition and only those rows
from the other table where the joined fields are equal (join condition is met).
Syntax
The syntax for the SQL RIGHT OUTER JOIN is:
SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON [Link] = [Link];

In some databases, the RIGHT OUTER JOIN keywords are replaced with RIGHT JOIN.

SELECT * FROM
COUNTRIES
RIGHT JOIN CITIES
On COUNTRIES.COUNTRY_ISO_CODE=CITIES. COUNTRY_ISO_CODE
And [Link] >[Link];

SQL FULL OUTER JOIN


Another type of join is called a SQL FULL OUTER JOIN. This type of join returns all
rows from the LEFT-hand table and RIGHT-hand table with nulls in place where the
join condition is not met.

Syntax
The syntax for the SQL FULL OUTER JOIN is:
SELECT columns
FROM table1
FULL [OUTER] JOIN table2
ON [Link] = [Link];

In some databases, the FULL OUTER JOIN keywords are replaced with FULL JOIN.

SELECT * FROM
COUNTRIES
FULL JOIN CITIES
On COUNTRIES.COUNTRY_ISO_CODE=CITIES. COUNTRY_ISO_CODE
And [Link] >[Link];

SQL NATURAL JOIN

A NATURAL JOIN is a JOIN operation that creates an implicit join clause for you
based on the common columns in the two tables being joined. Common columns are
columns that have the same name in both tables.

If the SELECT statement in which the NATURAL JOIN operation appears has an
asterisk (*) in the select list, the asterisk will be expanded to the following list of
columns (in this order):

 All the common columns


 Every column in the first (left) table that is not a common column
 Every column in the second (right) table that is not a common column

An asterisk qualified by a table name (for example, COUNTRIES.*) will be expanded to


every column of that table that is not a common column.

Syntax
Select *
FROM table1
NATURAL JOIN table2;

Examples

If the tables COUNTRIES and CITIES have two common columns named COUNTRY
and COUNTRY_ISO_CODE, NATURAL JOIN applies equality condition on both
attributes:

SELECT * FROM COUNTRIES NATURAL JOIN CITIES;

CROSS JOIN operation

A CROSS JOIN is a JOIN operation that produces the Cartesian product of two tables.
Unlike other JOIN operators, it does not let you specify a join clause. You may,
however, specify a WHERE clause in the SELECT statement.

Examples

The following SELECT statements are equivalent:

SELECT * FROM CITIES CROSS JOIN SELECT * FROM CITIES, FLIGHTS


FLIGHTS
Practical Assignment - 3

Department: Computer Engineering & Applications


Course: [Link]. (CSE)
Subject: Database Management System Lab (CSE3083)
Year: 2nd Semester:3rd

Run the following Script:

BEGIN
FOR cur_rec IN (SELECT object_name, object_type
FROM user_objects
WHERE object_type IN
('TABLE',
'VIEW',
'PACKAGE',
'PROCEDURE',
'FUNCTION',
'SEQUENCE'
))
LOOP
BEGIN
IF cur_rec.object_type = 'TABLE'
THEN
EXECUTE IMMEDIATE 'DROP '
|| cur_rec.object_type
|| ' "'
|| cur_rec.object_name
|| '" CASCADE CONSTRAINTS';
ELSE
EXECUTE IMMEDIATE 'DROP '
|| cur_rec.object_type
|| ' "'
|| cur_rec.object_name
|| '"';
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( 'FAILED: DROP '
|| cur_rec.object_type
|| ' "'
|| cur_rec.object_name
|| '"'
);
END;
END LOOP;
END;
/

commit;
drop table College;
drop table Student;
drop table Apply;

create table College(collegeName varchar2(10) primary key, state


varchar2(10), enrollment int);
create table Student(sIDint primary key, sName varchar2(10), GPA
real, sizeHSint);
create table Apply(sIDint, cName varchar2(10), major varchar2(20),
decision char(1), primary key(sID, major, cName), constraint sID_fk
Foreign key(sID) references Student, constraint cName_fk Foreign
key(cName) references College);

delete from Student;


delete from College;
delete from Apply;

insert into Student values (123, 'Amy', 3.9, 1000);


insert into Student values (234, 'Bob', 3.6, 1500);
insert into Student values (345, 'Craig', 3.5, 500);
insert into Student values (456, 'Doris', 3.9, 1000);
insert into Student values (567, 'Edward', 2.9, 2000);
insert into Student values (678, 'Fay', 3.8, 200);
insert into Student values (789, 'Gary', 3.4, 800);
insert into Student values (987, 'Helen', 3.7, 800);
insert into Student values (876, 'Irene', 3.9, 400);
insert into Student values (765, 'Jay', 2.9, 1500);
insert into Student values (654, 'Amy', 3.9, 1000);
insert into Student values (543, 'Craig', 3.4, 2000);
insert into College values ('Stanford', 'CA', 15000);
insert into College values ('Berkeley', 'CA', 36000);
insert into College values ('MIT', 'MA', 10000);
insert into College values ('Cornell', 'NY', 21000);
insert into College values ('Harvard', 'MA', 50040);
insert into Apply values (123, 'Stanford', 'CS', 'Y');
insert into Apply values (123, 'Stanford', 'EE', 'N');
insert into Apply values (123, 'Berkeley', 'CS', 'Y');
insert into Apply values (123, 'Cornell', 'EE', 'Y');
insert into Apply values (234, 'Berkeley', 'biology', 'N');
insert into Apply values (345, 'MIT', 'bioengineering', 'Y');
insert into Apply values (345, 'Cornell', 'bioengineering', 'N');
insert into Apply values (345, 'Cornell', 'CS', 'Y');
insert into Apply values (345, 'Cornell', 'EE', 'N');
insert into Apply values (678, 'Stanford', 'history', 'Y');
insert into Apply values (987, 'Stanford', 'CS', 'Y');
insert into Apply values (987, 'Berkeley', 'CS', 'Y');
insert into Apply values (876, 'Stanford', 'CS', 'N');
insert into Apply values (876, 'MIT', 'biology', 'Y');
insert into Apply values (876, 'MIT', 'marine biology', 'N');
insert into Apply values (765, 'Stanford', 'history', 'Y');
insert into Apply values (765, 'Cornell', 'history', 'N');
insert into Apply values (765, 'Cornell', 'psychology', 'Y');
insert into Apply values (543, 'MIT', 'CS', 'N');
commit;
Student Apply
sID sName GPA sizeHS sID cName major decision
123 Amy 3.9 1000 123 Stanford CS Y
234 Bob 3.6 1500 123 Stanford EE N
345 Craig 3.5 500 123 Berkeley CS Y
456 Doris 3.9 1000 123 Cornell EE Y
567 Edward 2.9 2000 234 Berkeley biology N
678 Fay 3.8 200 345 MIT bioengineering Y
789 Gary 3.4 800 345 Cornell bioengineering N
987 Helen 3.7 800 345 Cornell CS Y
876 Irene 3.9 400 345 Cornell EE N
765 Jay 2.9 1500 678 Stanford history Y
654 Amy 3.9 1000 987 Stanford CS Y
543 Craig 3.4 2000 987 Berkeley CS Y
876 Stanford CS N
876 MIT biology Y
College 876 MIT marine biology N
collegeName state enrollment 765 Stanford history Y
Stanford CA 15000 765 Cornell history N
Berkeley CA 36000 765 Cornell psychology Y
MIT MA 10000 543 MIT CS N
Cornell NY 21000
Harvard MA 50040

Write SQL Queries for the following:

Q1. Produce a combine table in which each student is combine with every other
application.
Q2. Give Student ID, name, GPA and name of college and major each student applied to.
Q3. Find detail of applications who applied to California State.
Q4. IDs, name, GPA of students and name of college with GPA > 3.7 applying to
Stanford
Q5. Find detail of Student who apply to CS major and their application are rejected
Q6. Find detail of student and application who applied to colleges at New York
Q7. Find detail of student who have not applied to any of college
Q8. Find college where no student have applied
Q9. Find sID who have only one application
Q10. Find name and GPA of applicants who apply to any college whose enrollment is not
more than 25000.
Q11. Find pair of students (sID) having same GPA. (each pair should occur just once in
result)
Q12. Find various majors student applied in at college in state MA.
Exercise

For each of the following you need to write three queries


i.e. three version first using :CROSS Join
Second using: Natural Join
And third using: Inner Join

You are also advised to observe output of all three

Q13. find student and major he / she applied to.


Q14. Find detail of student who came from high school have size less than 20000 and
applied to CS at Stanford.
Q15. Provide complete detail of each student where they applied what major they applied
to what was the decision and complete detail of college they applied.
Q16. Names and GPAs of students with HS>1000 who applied to CS and were rejected
Q17. Names and GPAs of students with HS>1000 who applied to CS at college with
enr>20,000 and were rejected

Pre Experiment Questions


1. When we need to combine two tables?
2. Difference between Equi Join and Theta Join
3. Difference between Natural join and Inner Join
Post Experiment Questions
1. When can we use natural join?
2. When we are bound to use inner join?
3. Can we implement all joins using cross join?
4. Where and in what kind of queries require outer joins?

Common questions

Powered by AI

SQL INNER JOIN requires explicitly specifying the columns on which to join the tables, allowing for precise control over the join conditions. It is best used when specific columns must be matched across tables . In contrast, SQL NATURAL JOIN automatically joins tables based on common column names without explicitly specifying the columns, which can simplify queries but may lead to unexpected results if common columns do not exist or differ. NATURAL JOIN is useful for quick combination of tables when the schema design ensures identical column names for join conditions .

A CROSS JOIN produces the Cartesian product of two tables, which means every row from the first table is combined with every row from the second table, resulting in a large number of combinations compared to other joins . Unlike INNER, LEFT, RIGHT, and FULL OUTER joins, CROSS JOIN does not require or allow a join condition and may lead to extremely large datasets with many irrelevant combinations if misused, potentially impacting query performance critically .

To use a NATURAL JOIN for this query, first ensure common columns exist. Assuming matching column names across tables, the query would be: SELECT * FROM Student NATURAL JOIN Apply NATURAL JOIN College WHERE College.state = 'NY' . NATURAL JOIN's implicit nature can lead to unexpected results if non-key attributes coincidentally match across tables or schema design changes, so schema awareness is critical to mitigate these limitations.

SET operations in SQL, such as UNION, INTERSECT, and EXCEPT/ MINUS, combine results from multiple queries, differing fundamentally from JOINs by operating on the results instead of row-wise datasets. While JOINs combine columns from tables, SET operations combine query results, enabling more complex manipulations like filtering distinct data points or uniting different sub-query results . SET operations are useful for combining datasets that have similar columnar data but vary in result source, while JOINs are optimized for retrieving related detail across multiple table relationships.

The PL/SQL for object management uses EXECUTE IMMEDIATE to perform dynamic DROP operations, offering flexibility and ease of maintenance. However, it poses risks like accidental loss of objects or dependencies and performance overhead if not adequately managed . Mitigation measures include implementing logging for auditing, utilizing conditional checks prior to drops, and running as a part of controlled maintenance routines, minimizing unintended consequences and preserving critical data structures.

You can retrieve these details using an INNER JOIN as follows: SELECT Student.sID, Student.sName, Student.GPA, College.collegeName, Apply.major FROM Student INNER JOIN Apply ON Student.sID = Apply.sID INNER JOIN College ON Apply.cName = College.collegeName . This statement joins the Student, Apply, and College tables on matching student IDs and college names to gather the desired information.

To find pairs of students with the same GPA, you can use a self-join on the Student table that links each student to others who have the same GPA value. The query can be structured as follows: SELECT s1.sID, s2.sID FROM Student s1 INNER JOIN Student s2 ON s1.GPA = s2.GPA AND s1.sID < s2.sID . This query avoids duplicate pairs by ensuring that s1.sID is less than s2.sID.

A SQL FULL OUTER JOIN returns all rows from both the left and the right tables, filling with nulls where the join condition is not met, thus providing a complete set of data from both tables . A RIGHT OUTER JOIN, on the other hand, returns all rows from the right table and only matched rows from the left table, filling with nulls on the left side where no matches occur . FULL OUTER JOIN provides more comprehensive data by including all possible combinations and unmatched data from both tables.

The provided PL/SQL block iterates over user objects like tables, views, packages, procedures, functions, and sequences, dynamically executing DROP statements based on object type. For tables, it includes CASCADE CONSTRAINTS to remove dependencies . Well-structured error handling ensures that failures are logged without terminating the loop. This approach effectively manages object cleanup in an Oracle environment, but its effectiveness depends on accurate filtering and the need for a comprehensive log for audit purposes.

A SQL REJECTION query can be constructed by filtering the Apply table for specific majors and decisions. The query is: SELECT Student.sID, Student.sName, Apply.major FROM Student INNER JOIN Apply ON Student.sID = Apply.sID WHERE Apply.major = 'CS' AND Apply.decision = 'N' . This identifies students with 'CS' as their major choice whose application response was 'N' for declined.

You might also like