What are joins? Why they are used.
- A SQL server Join is helps to query data from two or more tables between columns of these tables. A simple JOIN returns data for at least one match between the tables. The columns need to be similar. Usually primary key one table and foreign key of another is used.
- Jojns can be used to temporarily create 'complete' records from a database which may split related data across several tables.
Syntax:Table1_name JOIN table2_name ON table1_name.column1_name= table2_name.column2_name
Explain Inner join, Self join, Natural join and outer joins with examples.
1. INNER JOIN : Inner join returns rows when there is at least one match in both tables.
Syntax:SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name
Example : To display records of an employee who got an appraisal.
SELECT employee.firstname, appraisal.amount FROM employee INNER JOIN appraisal ON employee.id = appraisal.employee.id;
2. OUTER JOIN : In An outer join, rows are returned even when there are no matches through the JOIN criteria on the second table.
3. SELF JOIN : It is a join in which a table is joined or compared to itself. Self-joins are used to compare values in a column with other values in the same column in the same table
Example:SELECT DISTINCT c1.ContactName, c1.Address, c1.City, c1.Region FROM Customers AS c1, Customers AS c2 WHERE c1.Region = c2.Region AND c1.ContactName <> c2.ContactName ORDER BY c1.Region, c1.ContactName;
4. NATURAL JOIN : Joins two tables based on common column names. Hence one must confirm the common columns before using a NATURAL JOIN
Example:SELECT emp_firstname, appraisal_amt FROM employee NATURAL JOIN appraisal
What is full join. Explain with an example.
Full join is used to return records from both left and right outer join. The joined table contains records from both tables. It contains NULL values for the missing matches on either tables.
Example : To display employees who have a bonus and to display bonus even if he is not an employee.
Select * From employee FULL OUTER JOIN bonus
ON employee.bonusID=bonus.bonusID
How to optimize joins in MySQL?
MySQL joins two tables and reads the rows from the first table. For each row of first table it finds matching row on the second. Creating an index on the join column reduces execution time and increases response time. The order in which tables are joined also matters. Join optimizer calculates the order in which tables should be joined. STRAIGHT_JOIN can be used to ensure that join order is from the smallest table to the largest.
