SQL inner and outer joins | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
we will use our tables from the previous posting:
Inner JOINAn inner join simply tells the server to return only records that matches in both left and right table, but using the table on the left as the main or leading table. For example, take a look at the following query: select employeeid, firstname,lastname, departmentname from department a INNER JOIN employee b on a.departmentid = b.departmentid;
Just like we saw before, we could still restrict the results to a specific department if we need to, that can be achieved by putting the query conditions in the where clause as we saw in SQL cross and equi-Joins. Outer JOINOuter joins come in two sets, one is called RIGHT OUTER JOIN and the LEFT OUTER JOIN . The right outer join, you tell the server to return the full set of rows in the right table and put NULLs in the fields of the left table that do not match. Say for example, if your right table was department,the query would return all rows in the department table, if there are matching rows in the employee table (on departmentid), those values are returned other wise they will be filled with NULLs (Nothing). Example: select employeeid, firstname,lastname, departmentname from employee a right outer join department b on a.departmentid = b.departmentid;Results:
As you can see here, All departments were returned and those with matching records in the employee table returned appropriate data. Since the IT department(departmentid = 4) does not have any employees assigned, the names and employeeid fields returned nothing. LEFT OUTER JOINThis is the exact opposite of right outer join. This one returns all rows from the left table and corresponding records in the right table. For example: select employeeid, firstname,lastname, departmentname from employee a left outer join department b on a.departmentid = b.departmentid;Returns:
Now all rows in the employee table were returned and matched with departments, the reason you do not see any NULLs or empty records here is because, every record in employee table has a match in the department table. |
RELATED ARTICLES:
SQL cross and equi-joins
In the posting , Selecting data from Multiple tables, we briefly covered ways of selecting data from
SQL SELECT Queries
So, we have created your database, added data into the tables and now what? In real world nobody collects data for the sake of collecting something. It has to be something that they can use at a later time. This section is dedicated to retrieving information stored in a da
Copying data from one table to the other
here are several ways to copy or move data from one table to another. Some methods are applicable when copying data from one table to another on the same database and other methods are applicable for copying data from one database to another and even or a different server. Bellow are several methods
Common SQL Data types
Now that you have identified your data entities or tables, you have to define each attribute of the tables you have identified. These definitions include the name of the attribute and the type of data that can be stored in the field. Most relational database systems have a number of data types that
Creating database tables with SQL
To create a table in SQL, you use a command that requires the name of the table as well as names and data types of the fields of that table. In general, the syntax for creating a table is as follows: CREATE table_name ( column_name data_type); The table nam
Database Design
After gathering and analyzing your requirements, and depending on whether or not your application needs a database to store information; database design is the next big thing. This comes even before you start coding the application program. The reason for this is that, the application program
Selecting data from multiple tables
So far we have covered methods of retrieving data from single table. In real life however, you hardly get to only retrieve data from one table. In other words, to retrieve and interpret information, you normally get such information from different tables each of which will contain only information a
Updating and deleting data with SQL
In the posting “Introduction to SQL”, we covered the creation of data storage space or database and its entities. We then went a little further to cover a methods of entering data into those entities or tables and methods of retrieval. There are however, times when the data stored in
The state of Periodic table elements
- In addition to the group 8 or O elements, the following elements also naturally occur in gas state: H, N, O, F, Cl and Br -Among metals and non-metals, the following occurs in liquid state: Na, K, Rb, Cs, Fr, Hg a
compound interest heats of reaction