![]() ![]() Now let's query our database for our departments and their locations:įirst_name | last_name | job_title | manager Let’s look at the structure for the locations table: Column Has a location_id referring to the locations table, which records whereĮach office is located. We already saw how our HR database keeps track of departments and each department ![]() In a RIGHT OUTER JOIN all rows from the right side table will be returned,Īnd NULL will be returned for the non-matching left side table rows. Had we used an INNER JOIN then only employees with dependents would have Smith, and the employee Howard TheDuck has none. PostgreSQL database returned that John Smith has two dependents: Spoiled and DoNothing +-+-+-+-ġ | John | Smith | Management | Spoiled Smithġ | John | Smith | Management | DoNothin SmithĢ | Pointy | HairedBoss | IT | Rafael HairedBossĢ | Pointy | HairedBoss | IT | Beta HairedBossģ | Dilbertzoni | DeScrewed | IT | Wanda DeScrewedĤ | Vanda | Socialist | Human Resources |īut why? Well, because some employees may not have any dependents, and we needĪ list of all employees, even if they have no dependents. Let's say in addition to the departments, we also need the first and lastĮmployee_id | first_name | last_name | department_name | dependent In our HR database, our employees can have dependents: Dependents Column In a LEFT OUTER JOIN all rows from the left side table will be returned,Īnd NULL will be returned for the non-matching right side table rows. There are different kinds of OUTER JOINs. Table that does not have match (as a side note, NULL means "undefined" in SQL). With those mismatched sets of data, by returning NULL values for the In the real world things don't always match perfectly. To write a specific program to parse and retrieve the information. Took care of finding and responding with that data, without the need for us ![]() We told the database server what information we wanted, and the database Where the department_id matches on both tables. The intersection of the table employees and the table departments We match these two sets of information in the FROM part of the query. The table employees and department_name from the table departments. Note how we request employee_id, first_name and last_name from INNER JOIN departments d ON (e.department_id = d.department_id) So let's say we want to query the employees and the departments they are aĬopy SELECT e.employee_id, e.first_name, e.last_name, d.department_name Which department each employee is a part of. The employee such as first and last name, a manager_id that refers to anotherĮmployee in the same table, and a department_id which tells our system Reference to the locations table, so we can know which office thisĪs for the employees table we have: Employees columnĮach employee record/row has an employee_id, things we need to know about Let's take a look at the structure for those tables: Departments columnįor each department we record its id, name, and a location_id that is a In our HR database we would like to find (query) employees and theirĭepartments. This application keeps track of departments, employees and theirĪn INNER JOIN is the most common, and matches a column in one table to another. Joins are the mechanism to find information in one set (table) thatįor these examples we'll look at a hypothetical database for a human resourcesĪpplication. In relationalĭatabases we organize data in sets, that are called tables in SQL. We store data in relational databases so we can organize and quicklyįind information we need. ![]()
0 Comments
Leave a Reply. |