Skip to content
  • Facebook
GeekCer Logo

GeekCer

The geek's Coding education and Review centre

  • Home
  • Tutorials
    • Java
    • Servlet
    • JSP
    • Python
    • C Tutorial
    • Spring
    • Spring Boot
    • MongoDB
    • Hibernate
    • Data Structure
  • General Knowledge
  • Biography
  • Grammar
  • Festival (त्योहार)
  • Interview
  • Differences
  • Important
  • Toggle search form

Home » Important » Join Queries in Oracle, Learning Advance Sql Joins, Example

  • Newton's laws of Motion, State and Explained, Formula
    Newton’s laws of Motion, State and Explained, Formula Science
  • Vat Savitri Vrat in Hindi, Vat Savitri Puja | वट सावित्री पूजा
    Vat Savitri Vrat in Hindi, Vat Savitri Puja | वट सावित्री पूजा Festival
  • Hima Das Biography | भारतीय धाविका हिमा दास का जीवन परिचय
    Hima Das Biography | भारतीय धाविका हिमा दास का जीवन परिचय Biography
  • Chhath Puja Story
    Chhath Puja History : क्यों मनाते हैं छठ महापर्व Festival
  • Mahatma Gandhi Essay in Hindi | Gandhiji Biography
    Mahatma Gandhi Essay in Hindi | Gandhiji Biography Biography
  • Holi kyon manate hain in hindi? | Festival of colors in hindi
    Holi kyon manate hain in hindi? | Festival of colors in hindi Festival
  • Ramayana Uttar Kand Luv Kush| रामायण उत्तर कांड इन हिंदी
    Ramayana Uttar Kand Luv Kush | रामायण उत्तर कांड इन हिंदी Spiritual
  • Vedaant Madhavan Biography in Hindi, Family, School, Age
    Vedaant Madhavan Biography in Hindi, Family, School, Age Biography

Join Queries in Oracle, Learning Advance Sql Joins, Example

Posted on February 5, 2022February 5, 2022 By GeekCer Education No Comments on Join Queries in Oracle, Learning Advance Sql Joins, Example
Join Queries in Oracle, Sql Joins, Self, Inner, Outer, Equi

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
  • Inner Joins or Equi Joins or Simple Join queries
  • Non Equi Join in SQL or Between Join Queries
  • SQL Outer Join Queries (Oracle Style)
  • SQL Table Aliases in Join Queries
  • Inner Join (ANSI or ISO Style)
  • Left Outer Join in Sql (ANSI or ISO Style)
  • Right Outer Join in Sql (ANSI or ISO Style)
  • Full Outer Join in Sql (ANSI or ISO Style)
  • What is cross join in SQL?
  • Sql join with more than two tables

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.

Share this:

  • Click to share on Facebook (Opens in new window)
  • Click to share on WhatsApp (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • More
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Pinterest (Opens in new window)

Also Read

Important Tags:Sql join multiple tables, Sql join multiple tables with conditions, What are different types of joins in SQL?, What is SQL joins with example?

Post navigation

Previous Post: Difference between Bugs, Errors and Issues in Software Testing
Next Post: Difference Between get() and load() in Hibernate with example

More Related Articles

Java 8 interview questions and answers, Java Stream, Optional Java 8 interview questions and answers, Java Stream, Optional Important
What is Internship? How to get Internship? What is Internship and how to get Internship? Important
Jenkins java | Installing Jenkins on Windows Jenkins java | Installing Jenkins on Windows Important
Agile Methodology of Software Development Agile Methodology of Software Development Important
Linux Commands With Example | Linux commands cheat sheet Linux Commands With Example | Linux commands cheat sheet Important
DBMS in Hindi | DBMS क्या है? | DBMS की विशेषताएं और प्रकार DBMS in Hindi | DBMS क्या है? | DBMS की विशेषताएं और प्रकार Important

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

  • National Farmers Day in Hindi | राष्ट्रीय किसान दिवस पर निबंध | चौधरी चरण सिंह जयंती
  • Human rights day in Hindi: 10 दिसंबर ह्यूमन राइट्स डे
  • Unicef day is celebrated on December 11 | Speech on unicef day
  • Indian Navy Day: जल सेना दिवस कब और क्यों मनाया जाता है?
  • P V Sindhu Biography in Hindi, Badminton, State, Caste पी. वी. सिंधु जीवन परिचय, कहानी, राज्य, जाति
  • Draupadi Murmu Biography In Hindi | द्रौपदी मुर्मू की जीवनी
  • TCP/IP Model, Full Form, Layers and their Functions
    TCP/IP Model, Full Form, Layers and their Functions Networking
  • IPv4 Vs IPv6 | Difference between IPv4 and IPv6
    IPv4 Vs IPv6 | Difference between IPv4 and IPv6 Differences
  • Similarities and difference between OSI and TCP/IP model
    OSI vs TCP/IP Model, Similarities and difference between OSI and TCP/IP model Networking
  • Network kya hai (नेटवर्क क्या है)
    Network kya hai (नेटवर्क क्या है) Networking
  • Difference between Internet and Intranet
    Difference between Internet and Intranet Differences
  • OSI Model | 7 Layers of OSI Model in Computer network
    OSI Model | 7 Layers of OSI Model in Computer network, Functions Networking
  • Difference between TCP and UDP
    Difference between TCP and UDP | TCP vs UDP examples Differences
  • Java Tutorial
  • Servlet Tutorial
  • JSP Tutorial
  • Maven Tutorial
  • HTML Tutorial
  • Programs
  • Hindi/English Grammar
  • Difference Between ... and ...
  • HR Interview
  • Important Articles

Write to Us:
geekcer.code@gmail.com

  • About Us
  • Privacy and Policy
  • Disclaimer
  • Contact Us
  • Sitemap

Copyright © GeekCer 2022 All Rights reserved