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.
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.
Table 1 for Login
Table 2 for Employee
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
- 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:
- Left outer join
- Right outer join
- 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: A 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.
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_id, name, 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.
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.
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 firstname.lastname@example.org