UrbanPro
true

Find the best tutors and institutes for Oracle Training

Find Best Oracle Training

Please select a Category.

Please select a Locality.

No matching category found.

No matching Locality found.

Outside India?

Search for topics

How to use joins in SQL

Sagar Waikar
02/12/2020 0 0

What are Joins in SQL?

  • Joins in SQL are used to combine rows between two or more tables.
  • Joins are used in SQL when user need to extract data between two or more tables based on a related column.
  • Joins returns the data between the tables based on the type of join used.
  • There are different types of joins available in SQL

What are the types of joins?

  • Inner Join or Simple Join
  • Left Join
  • Right Join
  • Full Join

Let’s discuss Inner join or simple join in detail.

Inner Join

  • Inner join is also called as Simple Join.
  • Inner join gives matching data from both the tables based on a related column.
  • Inner Join is used by mentioning INNER JOIN keyword in the query.

How to join?

  • Use Keyword INNER JOIN
  • Mention INNER JOIN after FROM in the query.
  • Mentioning INNER keyword is not mandatory.
  • Mention ON keyword before the join condition

Syntax:

SELECT column1, column2 from table1 INNER JOIN table2 ON table1.fieldname = table2.fieldname

LEFT Join

What is left join?

  • Left join returns all the rows from the left table.
  • Left join returns the rows even if there are no matching rows available in the right table.
  • Left join returns null to the columns of the rows where there are no matches found from the right table.
  • Left join returns all the values from the left table, plus matched values from the right table or NULL in case of no matching join condition.

How to use a LEFT join

  • Use Keyword LEFT
  • Mention LEFT JOIN after FROM in the query.
  • Mentioning LEFT keyword is mandatory.
  • Mention ON keyword before the join condition

Syntax:

SELECT column1, column2 From table1 LEFT JOIN table 2

ON table1.common_field = table2.common_field

RIGHT JOIN

What is Right, Join?

  • Right, join returns all the rows from the right table.
  • Right, join returns the rows even if there are no matching rows available in the left table.
  • Right, join returns null to the columns of the rows where there are no matches found from the left table.
  • Right join returns all the values from the right table, plus matched values from the left table or NULL in case of no matching join condition.

How to use a RIGHT join

  • Use Keyword RIGHT
  • Mention RIGHT JOIN after FROM in the query.
  • Mentioning the RIGHT keyword is mandatory.
  • Mention ON keyword before the join condition

Syntax:

SELECT column1, column2 From table1 RIGHT JOIN table 2

ON table1.common_field = table2.common_field

FULL JOIN

What is Full Join?

  • Full join returns all the rows from the right and left the table.
  • Full join returns the nulls if there is no matching in the left or right table.
  • FULL JOIN can potentially return very large result-sets!

How to Join?

  • Use Keyword FULL
  • Mention FULL OUTER JOIN after FROM in the query.
  • Mentioning FULL OUTER keyword is mandatory.
  • Mention ON keyword before the join condition

Syntax:

SELECT column_name(s)

FROM table1

FULL OUTER JOIN table2

ON table1.column_name = table2.column_name

WHERE condition;

0 Dislike
Follow 2

Please Enter a comment

Submit

Other Lessons for You

How does a SQL Query Execute inside Oracle? What are the steps followed in the background?
The above snapshot says it all. Hence it is said, 'Picture says it all'. Basically, there are 4-5 steps. There are two possible ways of query being executed. 1. Soft Parse- Improves performance....

Gavi Y. | 05/01/2020

0 0
0

Interview questions based on "level", a pseudocolumn
1. Write a query to get the below output, 1 11 21 31 .. .. 91 2 12 22 32 .. .. 92 3 13 23 33 .. .. 93 .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. 10 20 30 40 ...

Gavi Y. | 05/01/2020

0 0
0

Sub Queries in SQL(Structured Query Language)
Writing the Query in one more Query is called Sub Query. In Oracle SQl Sub Queries are Diveded into two categories Simple Sub Queries Cor-related Sub Queries The difference between Simple Sub Queries...

Phanindra Oruganti | 15/12/2018

0 0
0

SQL - Structured Query Language - DQL Commands - SELECT
Structured Query Language allows to store , manipulate, control and retrieve the information from Relational Database Management Systems. Following are some RDBMS products. Oracle Databse, Microsoft SQL...

Kasyap Kasyap | 29/12/2016

2 0
1

How to read and understand the Compilation errors?
Today, we will understand how to interpret the compile time erros and fix them. Don't confuse with runtime errors which is altogether a different subject called Exception Handling. Compilation erros are...

Gavi Y. | 26/12/2016

0 0
0
X

Looking for Oracle Training Classes?

Find best tutors for Oracle Training Classes by posting a requirement.

  • Post a learning requirement
  • Get customized responses
  • Compare and select the best

Looking for Oracle Training Classes?

Find best Oracle Training Classes in your locality on UrbanPro

Post your learning requirement

UrbanPro.com is India's largest network of most trusted tutors and institutes. Over 55 lakh students rely on UrbanPro.com, to fulfill their learning requirements across 1,000+ categories. Using UrbanPro.com, parents, and students can compare multiple Tutors and Institutes and choose the one that best suits their requirements. More than 7.5 lakh verified Tutors and Institutes are helping millions of students every day and growing their tutoring business on UrbanPro.com. Whether you are looking for a tutor to learn mathematics, a German language trainer to brush up your German language skills or an institute to upgrade your IT skills, we have got the best selection of Tutors and Training Institutes for you. Read more