Informatics Practices
How is a left join different from a natural join ? Give example.
SQL Joins & Grouping
1 Like
Answer
In a LEFT JOIN, all rows from the left table are included along with matching rows from the right table, and NULL values are used for non-matching rows in the right table. Conversely, a natural join is a join where only one of the identical columns from the joined tables exists.
1. LEFT JOIN Example:
SELECT name, lastname
FROM empl LEFT JOIN dept ON empl.id = dept.id;
The result of this query would return name and lastname values from the empl table and all available values from the dept table. NULL is returned for non-existing values in dept table.
2. NATURAL JOIN Example:
SELECT * FROM empl NATURAL JOIN dept;
The query returns all columns from empl and dept tables, with rows having matching values in their common columns.
Answered By
3 Likes
Related Questions
In a Database Karnataka_Sangam there are two tables with the instances given below :
Table : STUDENTS
ADMNO NAME CLASS SEC RN ADDRESS PHONE 1211 Meena 12 D 4 A-26 3245678 1212 Vani 10 D 1 B-25 5456789 1213 Meena 12 A 1 1214 Karish 10 B 3 AB-234 4567890 1215 Suraj 11 C 2 ZW12 4345677 Table : SPORTS
ADMNO GAME COACHNAME GRADE 1215 Cricket Mr. Ravi A 1213 Vollyball Ms. Chadha B 1211 Vollyball Mr. Govardhan A 1212 Basket Ball Mr. Tewani B Write SQL queries for the following :
(i) To count how many addresses are not having NULL values in the address column of STUDENTS table.
(ii) To display Name, Class from STUDENTS table and the corresponding Grade from SPORTS table.
(iii) To display Name of the student and their corresponding Coachnames from STUDENTS and SPORTS tables.
In a database BANK, there are two tables with a sample data given below:
Table: EMPLOYEE
ENo EName Salary Zone Age Grade Dept 1 Mona 70000 East 40 A 10 2 Mukhtar 71000 West 45 B 20 3 Nalini 60000 East 26 A 10 4 Sanaj 65000 South 36 A 20 5 Surya 58000 North 30 B 30 Table: DEPARTMENT
Dept DName HOD 10 Computers 1 20 Economics 2 30 English 5 (a) To display ENo, EName, Salary and corresponding DName of all the employees whose age is between 25 and 35 (both values inclusive).
(b) To display DName and corresponding EName from the tables DEPARTMENT and EMPLOYEE (Hint: HOD of DEPARTMENT table should be matched with ENo of EMPLOYEE table for getting the desired result).
(c) To display EName, Salary, Zone and Income Tax (Note: Income tax to be calculated as 30% of salary) of all the employees with appropriate column headings.
How is a cross join different from natural join ? Give example.
A table "TRAINS" in a database has degree 3 and cardinality 8. What is the number of rows and columns in it ?