Downloaded 13 times
![NON EQUI JOIN
• Syntax…
• SELECT *
• FROM Table_name1, Table_name2
• Where Table_name1.column [< | > |>= |<= ] Table_name2.column ;](https://image.slidesharecdn.com/sqljoins-180505171523/75/SQL-Join-s-7-2048.jpg)
![Right Join (Right Outer join) in Oracle
• Syntax....
• SELECT columns
• FROM table1
• RIGHT [OUTER] JOIN table2
• ON table1.column =
table2.column;](https://image.slidesharecdn.com/sqljoins-180505171523/75/SQL-Join-s-21-2048.jpg)
![Full Join (Full Outer Join) in Oracle
• Synatx:
• SELECT columns
• FROM table1
• FULL [OUTER] JOIN table2
• ON table1.column =
table2.column;](https://image.slidesharecdn.com/sqljoins-180505171523/75/SQL-Join-s-26-2048.jpg)
Uploaded byMuhammad Noman Fazil
SQL Join's
SQL JOINS combine data from two or more tables by matching column values. The major JOIN types are INNER, LEFT OUTER, RIGHT OUTER, and FULL OUTER JOINS which return matched and unmatched rows based on criteria. JOINS improve performance over subqueries and are used with other SQL clauses like GROUP BY, WHERE, and functions.
More Related Content
SQL Join's
- 2.
- 3. What is SQLJoins ❖ JOIN clause combines rows from two or more tables. ❖ creates a set of rows in a temporary table.
- 4.
- 5. Types of SQLJOIN • INNER JOIN • OUTER JOIN
- 6. Types of InnerJOIN • NON EQUI JOIN • EQUI JOIN • Natural JOIN
- 7. NON EQUI JOIN •Syntax… • SELECT * • FROM Table_name1, Table_name2 • Where Table_name1.column [< | > |>= |<= ] Table_name2.column ;
- 8. Types of OuterJoin • LEFT JOIN OR LEFT OUTER JOIN • RIGHT JOIN OR RIGHT OUTER JOIN • FULL OUTER JOIN
- 9. List of SQLJOINS • INNER JOIN • LEFT JOIN OR LEFT OUTER JOIN • RIGHT JOIN OR RIGHT OUTER JOIN • FULL OUTER JOIN • NATURAL JOIN • CROSS JOIN • SELF JOIN
- 10. INNER JOIN • Matchingcolumn values
- 11. Syntax • SELECT columnlist •FROM lefttable • Join-type right table • ON (join_Condition)
- 12. Example SELECT employee.lastName,employee departmentID,department.DepartmentNa me FROM employeeINNER JOIN Department ON employee .DepartmentID = department.DepartmentID; Employee. LastName Employee.D epartmentI D Department .Departmen tName Robinson 34 Clerical Jones 33 Engineering Smith 34 Clerical Heisenberg 33 Engineering Rafferty 31 Sales
- 13. IN MYSQL • SELECTA.`first_name` , A.`last_name` , B.`title` • FROM `members`AS A • INNER JOIN `movies` AS B ON B.`id` = A.`movie_id
- 14. Inner Join (simplejoin) in Oracle • Syntax.... • SELECT columns • FROM table1 • INNER JOIN table2 • ON table1.column = table2.column;
- 15. Example supplier_id supplier_name 10000 IBM 10001Hewlett Packard 10002 Microsoft 10003 NVIDIA order_id supplier_id order_date 500125 10000 2003/05/12 500126 10001 2003/05/13 500127 10004 2003/05/14
- 16. Example supplier_id name order_date 10000IBM 2003/05/12 10001 Hewlett Packard 2003/05/13 • SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date • FROM suppliers • INNER JOIN orders • ON suppliers.supplier_id = orders.supplier_id;
- 17. LEFT JOIN • TheSQL LEFT JOIN, joins two tables and fetches rows based on a condition, which are matching in both the tables. • The unmatched rows will also be available from the table before the JOIN clause.
- 18. Example SELECT * FROM employee LEFTOUTER JOIN Department ON employee .DepartmentID = department.DepartmentID; Employee.La stName Employee.Depar tmentID Department.De partmentName Department.De partmentID Jones 33 Engineering 33 Rafferty 31 Sales 31 Robinson 34 Clerical 34 Smith 34 Clerical 34 Williams NULL NULL NULL Heisenberg 33 Engineering 33
- 19. RIGHT JOIN ORRIGHT OUTER JOIN • A RIGHT JOIN is similar to the Left Join only the roles are reversed. Basically you return all rows from the right table and the matched rows from the left table.
- 20. Example SELECT * FROM employee RIGHTOUTER JOIN Department ON employee .DepartmentID = department.DepartmentID; Employee.L astName Employee.D epartmentI D Department. Department Name Department. Department ID Smith 34 Clerical 34 Jones 33 Engineering 33 Robinson 34 Clerical 34 Heisenberg 33 Engineering 33 Rafferty 31 Sales 31 NULL NULL Marketing 35
- 21. Right Join (RightOuter join) in Oracle • Syntax.... • SELECT columns • FROM table1 • RIGHT [OUTER] JOIN table2 • ON table1.column = table2.column;
- 22. JOINS and theWHERE Clause • The WHERE clause specifies the relationship between tables listed in the FROM clause. • It also restricts the rows displayed in the result table. • The most commonly used JOIN operator is the "equal" (=) sign.
- 23. JOIN Three Tables •SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName FROM ((Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID) INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);
- 24. Full outer join •Reverse to Inner join • LEFT and RIGHT JOIN at the same time • Full join can return very large datasets.
- 25. Example SELECT * FROM employee FULLOUTER JOIN Department ON employee .DepartmentID = department.DepartmentID; Employee.L astName Employee.D epartmentID Department. Department Name Department. DepartmentI D Smith 34 Clerical 34 Jones 33 Engineering 33 Robinson 34 Clerical 34 Williams NULL NULL NULL Heisenberg 33 Engineering 33 Rafferty 31 Sales 31 NULL NULL Marketing 35
- 26. Full Join (FullOuter Join) in Oracle • Synatx: • SELECT columns • FROM table1 • FULL [OUTER] JOIN table2 • ON table1.column = table2.column;
- 27. NATURAL JOIN • TheSQL NATURAL JOIN is a type of EQUI JOIN and is structured in such a way that, columns with same name of associate tables will appear once only. • The columns must be the same data type.
- 28. Example • SELECT * •FROM Foods NATURAL JOIN Company;
- 29. "ON" and "USING"clauses • In above JOIN query examples, we have used ON clause to match the records between table. • USING clause can also be used for the same purpose. The difference with USING is it needs to have identical names for matched columns in both tables.
- 30. OTHERs… • Apart fromusing ON and USING with JOINs you can use many other MySQL clauses like GROUP BY, WHERE and even functions like SUM, AVG, etc.
- 31. CROSS JOIN • TheSQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table, if no WHERE clause is used along with CROSS JOIN.
- 32.
- 33. Example SELECT * FROM Employee CrossJOIN Department Employee.L astName Employee.D epartmentID Department. Department Name Department. DepartmentI D Rafferty 31 Sales 31 Jones 33 Sales 31 Heisenberg 33 Sales 31 Smith 34 Sales 31 Robinson 34 Sales 31 Williams NULL Sales 31
- 34. Syntax in Mysqlworkbench • SELECT * • FROM `movies` CROSS JOIN `members’ ;
- 35. SELF JOIN • Aself-join is joining a table to itself.
- 36. Example Employe eID LastNam e Employe eID LastNam e Country 123 Rafferty 124Jones Australia 123 Rafferty 145 Heisenb erg Australia 124 Jones 145 Heisenb erg Australia 305 Smith 306 Williams Germany SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country FROM Employee F INNER JOIN Employee S ON F.C ountry = S.Country WHERE F.EmployeeID < S.EmployeeID ORDER BY F.EmployeeID, S.EmployeeID;
- 37. POINT TO BENOTED • MySQL can achieve better performance with JOINs as it can use Indexing. Further it requires more data manipulations in application end also.
- 38.
- 39. Advanced Or IntelligentJOIN’s
- 40. SQL JOIN RFEFRENTIALIntegrity • RI is a DB concept that is used to build and maintain logical relationships between tables to avoid logical corruption of data • Important part of RDBMS.
- 41.
- 42. Summary • combine datafrom more than one table into a single result set. • better performance compared to sub queries • INNER JOINS only return rows that meet the given criteria. • OUTER JOINS can also return rows where no matches have been found. The unmatched rows are returned with the NULL keyword. • The major JOIN types include Inner, Left Outer, Right Outer, Cross JOINS etc. • The frequently used clause in JOIN operations is "ON". "USING" clause requires that matching columns be of the same name. • JOINS can also be used in other clauses such as GROUP BY, WHERE, SUB QUERIES, AGGREGATE FUNCTIONS etc.
- 43. Queries • Suppose thereare two tables category and product.Both table contains category_id. We want to fetch the records where both tables contain same category_id . • In Emp table there are two columns empno and mgr. Fetch the records where empno and mgr are same. • Retrieve each employee who is in a department and each department that has an employee and also each employee who is not part of a department and each department which doesn't have an employee.
- 44. Queries • Write aquery in SQL to display the first name, last name, department number and department name, for all employees for departments 80 or 40. • Write a query in SQL to display all departments including those where does not have any employee. • Write a query in SQL to display the first name of all employees and the first name of their manager including those who does not working under any manager.
- 45.
Editor's Notes
- #4 An SQL join clause combines columns from one or more tables in a relational database. It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining columns from one (self-join) or more tables by using values common to each.
- #5 You’re in a business that accumulates data, and to get a clear view, you need to understand what this data means. There are often duplicate entries in your data, and SQL JOIN clauses will help you find the relevant data you need. Using SQL JOIN clauses is perfect for combining rows from two or more tables and the common fields behind them.
- #6 INNER JOIN Returns only matched rows from the participating tables. Match happened only at the key record of participating tables. OUTER JOIN Returns all rows from one table and Matching rows from the secondary table and Comparison columns should be equal in both the tables.
- #7 EQUI JOIN EQUI JOIN is a simple SQL join. Uses the equal sign(=) as the comparison operator for the condition NON EQUI JOIN NON EQUI JOIN uses comparison operator other than the equal sign. The operators uses like >, <, >=, <= with the condition.
- #8 The SQL NON EQUI JOIN uses comparison operator instead of the equal sign like >, <, >=, <= along with conditions. The Theta join (also known as the Non-equi join) is the kind of join you'll produce if you replace or combine the equals operator with something else.
- #11 The INNER JOIN selects all rows from both participating tables as long as there is a match between the columns.An SQL INNER JOIN is same as JOIN clause, combining rows from two or more tables. The "explicit join notation" uses the JOIN keyword, optionally preceded by the INNER keyword, to specify the table to join, and the ON keyword to specify the predicates for the join, as in the following example:
- #18 Where no matches have been found in the table on the right, NULL is returned.
- #20 The SQL RIGHT JOIN, joins two tables and fetches rows based on a condition, which are matching in both the tables.The unmatched rows will also be available from the table written after the JOIN clause Where no matches have been found in the table on the left, NULL is returned.
- #25 Full join and Full outer Join are same. Combines the results of both left and right outer joins.Returns all matched or unmatched rows.Includes tables on both sides of the join clause.
- #28 The SQL NATURAL JOIN combines the rows from two tables that have equal values in all matched by name columnsThe associated tables have one or more pairs of identically named columns.The columns must be the same data type.Don’t use ON clause in a natural join.
- #32 All possible Combinations This kind of result is called as Cartesian Product. This joins everything to everything.If, WHERE clause is used with CROSS JOIN, it functions like an INNER JOIN. Its dangerous for large dataset tables
- #36 A self join is a join in which a table is joined with itself (Unary relationships), specially when the table has a FOREIGN KEY which references its own PRIMARY KEY.To join a table itself means that each row of the table is combined with itself and with every other row of the table.The self join can be viewed as a join of two copies of the same table.
- #38 Simply use of single JOIN query instead running multiple queries do reduce server overhead. Using multiple queries instead that leads more data transfers between MySQL and applications (software). It is clear that we can achieve better MySQL and application performances by use of JOINs
- #39 Before going to Advanced join we clearify the difference Now listen carefully Here we see that all the join retrieve matching and non matching parts accepting inner join
- #40 Before going to Advanced join we clearify the difference Now listen carefully Here we see that all the join retrieve matching and non matching parts accepting inner join Anyone can tell me the difference b/w them?? So I will tell you them The main difference is that they cant give or retrieve the matching row By using where clause For eg Select * form tbl1 left join tbl2 on tbl1.id=tbl2.id Where tbl1.id is null
- #42 These are the most common SQL JOIN types you’ll likely encounter and need. Before making a query, think about your data, the different scenarios and results you’d like to have and which SQL JOIN clause you should use. As short summary the chart below visualizes the different joins.
- #43 JOINS allow us to combine data from more than one table into a single result set. JOINS have better performance compared to sub queries INNER JOINS only return rows that meet the given criteria. OUTER JOINS can also return rows where no matches have been found. The unmatched rows are returned with the NULL keyword. The major JOIN types include Inner, Left Outer, Right Outer, Cross JOINS etc. The frequently used clause in JOIN operations is "ON". "USING" clause requires that matching columns be of the same name. JOINS can also be used in other clauses such as GROUP BY, WHERE, SUB QUERIES, AGGREGATE FUNCTIONS etc.
- #44 1: SELECT * FROM CATEGORIES C ,PRODUCTS P where P.CATEGORYID = C.CATEGORYID; SELECT * FROM CATEGORIES C INNER JOIN PRODUCTS P ON P.CATEGORYID = C.CATEGORYID 2: SELECT * FROM EMP E1, EMP E2 WHERE E1.EMPNO=E2.MGR 3: SELECT * FROM EMP FULL OUTER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO
- #45 SELECT E.first_name , E.last_name , E.department_id , D.department_name FROM employees E JOIN departments D ON E.department_id = D.department_id AND E.department_id IN (80 , 40) ORDER BY E.last_name; SELECT E.first_name, E.last_name, D.department_id, D.department_name FROM employees E RIGHT OUTER JOIN departments D ON E.department_id = D.department_id; SELECT E.first_name AS "Employee Name", M.first_name AS "Manager" FROM employees E LEFT OUTER JOIN employees M ON E.manager_id = M.employee_id;