![]() The ON condition indicates how the two tables (the one after FROMand the one after LEFT OUTER JOIN) should be combined. In Following SQL left join query, LEFT OUTER JOIN clause that is between tblemp and tbldept tables derived all the matched records from left table tblemp which are having Vadodara as city value and having dept_id of sales department. ![]() List Employee details living in Vadodara City and working in Sales Department with SQL Left join statement. SELECT tblemp.Emp_name AS Name, tblemp.street AS Street, tblemp.city AS City, tblemp.Emp_contact AS Contact, tblemp.Salary, tbldept.Dept_name AS Department We LEFT JOIN employee table (i.e., tblemp) with department table (i.e., tbldept) with LEFT JOIN and we will get all records of employee table whose is having Surat value in city whether it is having Dept_id value or not. In this example, we will display employee details for which having “Surat” value in city column along with department name from department table, as we have done in pervious example. List Employee details living in Surat city along with Department name by left joining tblemp to tbldept This means, if there are employee in tblemp that do not have any department in tbldept, those rows will also be listed. The LEFT JOIN can be used which returns all the rows from first table (i.e., tblemp), even if there are no matches in the second table (i.e., tbldept). SELECT tblemp.Emp_name AS Name, tblemp.street AS Street, tblemp.city, tblemp.Emp_contact AS Contact, tblemp.Salary, tbldept.Dept_name AS Department In above query we have used AS command to rename a column with an alias. In this example, the data required is all the employee details along with their department name, this means all the employee details have to be listed even though their corresponding department information is not present. How can we use Left Outer join to list Employee details with the Department name Table 2 is department table with dept_id as a primary key. Suppose we working with two tables, Table 1 is employee table with emp_id as a primary key and dept_id as a foreign key. ![]() Table1.matchingcolumnname=Table2.matchingcolumnname lumn1, lumn2, lumn1, are the name of the columns which you want to retrieve separated by comma. Here, Table1 would be consider as a left table and Table2 would be consider as a right table. The left join or the left outer join statement returns all the records from left table and also those records which satisfy a condition from the right table, also for the records having no matching values in right table, the output or resulting dataset will contain NULL values. USING is also combined with JOIN in a join condition, but it requires that the column name be identical in both joined tables. ON is used with JOIN in a join condition to identify which columns is each table to link and can be used with all types of joins. WHERE can be used to create a join between tables without using the keyword JOIN, but it can only be used for inner joins. The keywords used to specify join conditions are WHERE, ON, USING, NATURAL and CROSS. Using joins a user can extract data from two or more tables based on a related column between them. The joints are predominantly used when a user is trying to fetch or get data from tables which have one to many or many to many relationships between them.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |