Multi-table query for database SQL: Eg:table1:employees, table2:departments,table3:salary_grades; one: Inner connection: 1): Equivalent connection: The Department_ in the table employees ID matches the department_id in table Departmes Select E.last_name, d.department_idfrom employees e,departments Dwhere e.department_id = d.department_id-The disadvantage of an equivalent connection is that there is no match in the filter condition and cannot be selected; 2): Non-equivalent connection: The salary in the table employees according to the table Salary_ Rank assigned in grades: Select Last_Name, Salary,grade_levelfrom employees,job_gradeswhere salary between Lowest_sal and Highest_sal Two: External connection:--relative to the internal connection in the equivalent connection, the shortcomings of the equivalent connection is obvious, to want to show the mismatch, you want to use an external connection; 1): Left join: Right to match the left side of the content, the matching left side of the output, to the right of the equation to add ' (+) '; Select E.last_name, d.department_idfrom employees e,departments Dwhere e.department_id = d.department_id (+) 2): Right connection: Left to match the right side of the content, the match is not to the right of the original output, to the left of the equation to add ' (+) '; Select E.last_name, d.department_idfrom employees e,departments Dwhere E.DEPARTMENT_ID (+) = d.department_id--Outside the connection is also defective, its flaw is: Left and right connection can not be used at the same time, to want to output both sides do not match, need to use to SQL99: three: Sql99:--sql99 is another set of systems; 1):--for the above-mentioned external connection defects, SQL99 can be resolved using natural join: Select last_name, Department_idfrom employees E Natural JoinDepartments d--his flaw is to automatically match two tables with exactly the same column name and the same data type {all columns} (not just the target column) case, 2):--for the above 1) defects, consider using the following: select Last_Name, D.department_idfrom Employees e Join departments Don e.department_id = d.department_id--and where the meaning is the same 3): Select Last_Name, Department_idfrom employees Join Departmentsusing (department_id)--This method requires column names in two tables Chen and data type exactly 4): Left join: Select Last_Name, D.department_idfrom employees E LEFT OUTER JOIN departments Don e.department_id = D.DEPARTMENT_ID5): Right connection: Select Last_Name, D.department_idfrom Employees E right OUTER JOIN departments Don e.department_id = D.DEPARTMENT_ID6): Full connection: Select Last_nam E, D.DEPARTMENT_IDFROM employees e full OUTER JOIN departments Don E.DEPARTMENT_ID = d.department_id Four: Self-connect:--To implement query table Employe Es in last_name = ' Chen ' employee's boss's salary and Email:select Emp.last_name,emp.manager_id,managers.salary,managers.emailfrom Employees emp,employees Managerswhere emp.manager_id = managers.employee_id and lower (emp.last_name) = ' Chen '
Multi-table query for database SQL