JOIN concepts of Database – coderInMe

In the previous post, we learned about the normal forms in Normalization. Going further, today we read about JOIN concepts in DBMS.

Let’s get started.

DEFINITION:

A join is an SQL operation performed to establish a connection between two or more database tables based on matching columns, thereby creating a relationship between the tables. Most complex queries in an SQL database management system involve ‘join’ commands.

 

There are various types of join are available in database:

  • EQUIJOIN or NATURAL JOIN

When we want to simply join the two or more tables in a database we use EQUIJOIN for the combination of a collection of records of the tables.

For example,

Table 1 for Login

join concepts in Database

 

Table 2 for Employee

JOIN concepts of Database

 

MySQL > select user_name, password, name, dob, address from Login JOIN Employee where Login.user_name = Employee.user_name;

 

It will retrieve the columns of common user_name and the name, password, dob and address from both the tables like

 

User_name Password Name Dob address

 

  • Outer Join

A left or right outer join is a join where rows in one table that do not have a matching row in the second table are selected with null values for the unknown columns.

The outer join is of three types:

  1. Left outer join
  2. Right outer join
  3. Full outer join

 

Left outer join:   In this, the table returns the value which is present in the left table written in the query. For example,

SELECT table1.firstname, table2.Title FROM table1 LEFT JOIN table2 
ON table1.ID = table2.ID;

 

Right outer join:   In this, the table returns the value which satisfied the ‘where’ condition and exists in the right table written in the query. For example,

Select * from SELLORDERS as SO RIGHT OUTER JOIN PORTFOLIO as F on F.cid = SO.cid;

 

Full outer join:

To write a query that performs an outer join and returns all rows from A and B, extended with nulls if they do not satisfy the join condition (a full outer join).

 

  • INNER JOIN: An inner join (sometimes called a simple join) is a join of two or more tables that returns only those rows that satisfy the join condition.

 

  • SELF JOIN: self join is a join of a table to itself. This table appears twice in the FROM clause and is followed by table aliases that qualify column names in the join condition. To perform a self join, Oracle or MYSQL Database combines and returns rows of the table that satisfy the join condition. For example,
 SELECT e1.last_name, e2.last_name
 FROM employees e1, employees e2
 WHERE e1.manager_id = e2.employee_id
 AND
 e1.last_name LIKE 'R%';

 

In the above example, we are retrieving the Manager lastname who is also an employee of the company so both from the same table but name starting with R.

Solved question from hackerRank

HackerRank Ques. 1 

Given the CITY and COUNTRY tables, query the sum of the populations of all cities where the CONTINENT is ‘Asia’.

Note: CITY.CountryCode and COUNTRY.Codeare matching key columns.

Input Format

The CITY and COUNTRY tables are described as follows:

 

 

Julia asked her students to create some coding challenges. Write a query to print the hacker_idname, and the total number of challenges created by each student. Sort your results by the total number of challenges in descending order. If more than one student created the same number of challenges, then sort the result by hacker_id. If more than one student created the same number of challenges and the count is less than the maximum number of challenges created, then exclude those students from the result.

Input Format

The following tables contain challenge data:

  • Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker.

 

 

  • Challenges: The challenge_id is the id of the challenge, and hacker_id is the id of the student who created the challenge.

 

Solution :

 

select c.hacker_id, h.name, count(c.challenge_id) as total from challenges c

inner join hackers h
on h.hacker_id= c.hacker_id

group by c.hacker_id having 

total=
(select  count(c1.challenge_id) from challenges c1
group by c1.hacker_id 
ORDER BY count(c1.challenge_id) desc limit 1)

or 

count(c.challenge_id) in 
(select t1.tot from ((select t.tot, count(t.hacker_id) from (select count(c2.challenge_id) as tot, c2.hacker_id from challenges c2
group by c2.hacker_id) as t
group by t.tot having count(t.hacker_id)=1) as t1))

order by total desc, h.hacker_id;

 

Note: For any query or question please do comment and write me.We’ll try our best to solve your queries.

All rights reserved. No part of this Post may be copied, distributed, or transmitted in any form or by any means, without the prior written permission of the website admin, except in the case of brief quotations embodied in critical reviews and certain other noncommercial uses permitted by copyright law. For permission requests, write to the owner, addressed “Attention: Permissions Coordinator,” to the admin@coderinme.com

 

 

A Salesforce Developer at AlmaMate Info Tech PVT LTD. An Aligarian and also your query solver in database field.This site mark the difference as Black or White to make you choose the best for you. Don't feel pressurized, feel confident..!!

Leave a reply:

Your email address will not be published.