Join queries enable you to access data from two or more tables at the same time. Join queries are the most important aspect in the database. A join operation requires more than two tables, and to get the information from the tables, we need the where clause, which specifies the join’s information.
The where clause specifies the table name as well as the column that will be used in the join. In this process, we usually employ the primary key column and the foreign key column. Non-primary or non-foreign keys are also possible.
In this article we will learn different types of joins in Sql/DBMS with examples.
Table of Contents
JOIN Condition and JOIN Guidelines in SQL
The join condition is the circumstance under which we conduct a JOIN operation. The JOIN condition is specified in the where clause.
WHERE table1.column1 = table2.column2;
Inner Joins or Equi Joins or Simple Join queries
EQUI JOIN or INNER JOIN should be used to display just matched records. The joining table must contain a common column with the same data and data type, although the column might have a different name.
SELECT col1, col2, col3,... coln from <table1>, <table2>,... WHERE
<table1>.<common-col-name> = <table2>.<commmon-col-name> AND ....
SELECT player.playerid, playername, match_date, opponent from player, match
WHERE player.playerid = match.playerid;
PLAYERID PLAYERNAME MATCH_DAT OPPONENT
---------- -------------------- --------- --------------------
20 SMITH 10-JUL-20 WARD
30 ALLEN 12-FEB-21 JONS
20 SMITH 05-AUG-21 ADAMS
30 ALLEN 23-NOV-21 KING
Non Equi Join in SQL or Between Join Queries
Non-equi join queries define the connection between columns from several tables. When the value of one column of one table comes inside the range of two column values of another table, a non equi join query is used.
SELECT <table1>.column1, <table1>.column2, <table2>.column3... FROM <table1>, <table2>
WHERE <table1>.column-name [> | < | >= | <= ] <table2>.column-name;
SELECT player.playername, match.matchno, match.match_date,match.opponent FROM player, match
WHERE player.playerid < match.playerid;
PLAYERNAME MATCHNO MATCH_DAT OPPONENT
-------------------- ---------- --------- --------------------
CLARK 1 10-JUL-20 WARD
CLARK 2 12-FEB-21 JONS
SMITH 2 12-FEB-21 JONS
CLARK 3 05-AUG-21 ADAMS
CLARK 4 23-NOV-21 KING
SMITH 4 23-NOV-21 KING
SQL Outer Join Queries (Oracle Style)
In SQL, an outer join is a unique sort of join. When all records from one table are required, but only matching records from another table are required, in this case you can use SQL Outer Joins.
We employ the outer join operator (+) in outer join. When you use an outer join, all records in the second table that don’t match will be shown as a null value.
SELECT player.playerid, playername, match_date, opponent from player, match
WHERE player.playerid = match.playerid (+);
PLAYERID PLAYERNAME MATCH_DAT OPPONENT
---------- -------------------- --------- --------------------
20 SMITH 10-JUL-20 WARD
30 ALLEN 12-FEB-21 JONS
20 SMITH 05-AUG-21 ADAMS
30 ALLEN 23-NOV-21 KING
40 MILLER
10 CLARK
SELECT player.playerid, playername, match_date, opponent from player, match
WHERE player.playerid (+) = match.playerid;
SQL Table Aliases in Join Queries
When there are a lot of tables and the table name is lengthy, qualifying the table name takes a long time. In this instance, an alias table can be used.
Because of table aliases, SQL code is smaller and takes up less memory. To create a table alias, write the whole table name first, then a space, and then the table alias.
SELECT p.playerid, playername, match_date, opponent from player p, match m where p.playerid = m.playerid;
The alias of the Player table is p, and the alias of the Match table is m in the example above.
Inner Join (ANSI or ISO Style)
SELECT player.playername, opponent, match_date FROM player inner join match
ON (player.playerid = match.playerid);
Left Outer Join in Sql (ANSI or ISO Style)
SELECT player.playername, opponent, match_date FROM player left outer join match
ON (player.playerid = match.playerid);
Right Outer Join in Sql (ANSI or ISO Style)
SELECT player.playername, opponent, match_date FROM player right outer join match
ON (player.playerid = match.playerid);
Full Outer Join in Sql (ANSI or ISO Style)
Left Outer Join + Right Outer Join = Full Outer Join
SELECT player.playername, opponent, match_date FROM player full outer join match
ON (player.playerid = match.playerid);
What is cross join in SQL?
The CROSS JOIN joins two tables to produce a Cartesian product.
SELECT player.playername, opponent, match_date FROM player
cross join match WHERE player.playerid = match.playerid;
Sql join with more than two tables
More than two tables can be joined using JOINs. If you’re applying to more than two tables, start with the two most relevant tables and then move on to the third.
WHERE emp.deptno= dept.deptno AND salary.salno = emp.salno;
Above in the WHERE clause there are three tables which are EMP, DEPT and SALARY.
