You encounter the following statements:SELECT e1.ename|| 'works for '||e2.ename "Employees and their Managers" FROM emp e1, emp e2 WHERE e1.mgr=e2.empno; What kind of join is this?
Question
You encounter the following statements:SELECT e1.ename|| 'works for '||e2.ename "Employees and their Managers" FROM emp e1, emp e2 WHERE e1.mgr=e2.empno; What kind of join is this?
Solution
The SQL statement you provided is an example of an old style join, specifically an implicit Cartesian product, also known as a cross join.
Here's the step by step explanation:
-
The FROM clause
FROM emp e1, emp e2is joining two instances of the same tableemp. The aliasese1ande2are used to distinguish between the two instances. This is an old style join syntax which implicitly creates a Cartesian product of all rows frome1ande2. -
The WHERE clause
WHERE e1.mgr=e2.empnothen filters the result set of the Cartesian product to only include rows where themgrcolumn ofe1matches theempnocolumn ofe2. This effectively turns the cross join into an inner join. -
The SELECT clause
SELECT e1.ename|| 'works for '||e2.ename "Employees and their Managers"concatenates theenamecolumn frome1ande2with the string 'works for ' in between. This results in a string that shows which employee works for which manager.
So, while this SQL statement does not use the explicit JOIN syntax, it effectively performs an inner join operation.
Similar Questions
You have two tables employee and department that have 10 records. In the given context, determine the type of join being used in the query given alongside.SELECT employee.emp_id, department.emp_id, name, dept_id FROM employee, department; Inner JoinOuter JoinEqui-JoinCartesian Join
For the following table:Employees (EmployeeId, EmployeeName, HireDate, Salary, ReportsTo)You want to identify the supervisor to which each employee reports. You write the following query.SELECT e.EmloyeeName AS [EmployeeName], s.EmployeeName AS [SuperVisorName] FROM Employees eYou need to ensure that the query returns a list of all employees and their respective supervisor. Which join clause should you use to complete the query?INNER JOIN Employees s ON e.ReportsTo = s.EmployeeId;INNER JOIN Employees s ON e.EmployeeId = s.EmployeeId;INNER JOIN Employees s ON e.EmployeeId = s. ReportsTo;RIGHT JOIN Employees s ON e. EmployeeId = s. ReportsTo;
Write queries for the following tables:T1 ( Empno, Ename , Salary, Designation)T2 (Empno, Deptno.)(1) Display the Deptno in which Employee Seeta is working.(2) Display Empno, Ename, Deptno.
Evaluate below sql statement SELECT emp_name, department FROM employees WHERE department = 'HR' UNION SELECT contractor_name, department FROM contractors WHERE department = 'IT';Select one:a. Retrieves the names of all employees and contractors from the HR and IT departments.b. Retrieves the names of employees from the HR department and all contractors.c. Retrieves the names of employees and contractors from the HR and IT departments, excluding employees from the IT department and contractors from the HR department.d. Retrieves the names of employees from the HR department and contractors from the IT department.
To create a report displaying employee last names, department names, and locations. Which query should you use to create an equi-join?Select one:a.SELECT e.last_name, d.department_name, d.location_idFROM employees e, departments dWHERE manager_id =manager_id;b.SELECT last_name, department_name, location_idFROM employees , departments ;c.SELECT employees.last_name, departments.department_name,departments.location_id FROM employees e, departments dWHERE e.department_id =d.department_id;d.SELECT e.last_name, d.department_name, d.location_idFROM employees e, departments dWHERE e.department_id =d.department_id;
Upgrade your grade with Knowee
Get personalized homework help. Review tough concepts in more detail, or go deeper into your topic by exploring other relevant questions.