Database Systems with SQL - Full Course - Database Systems - Inner and Outer Joins

Reports are usually generated from data found in multiple tables. Join statements help combine the data from the two tables into a single result. The tables are combined by comparing columns, usually with the assignment operator =, from the left table and right table. Joins typically compares the foreign key of one table with the primary key of another table but can also compare any column with the same data type. Sometimes, the join tables contain columns with the same name. When this happens, you need to prefix the table name, followed by a period, and then the column name, so that you can distinguish them. The join query displays the JacksonFive’s names and ages. JacksonFive is the left table. SoloArtist is the right table. The query selects rows for which the primary key of JacksonFive equals the primary key Artist. Since we are using the same column name of two different table, we need to use the prefix s for SoloArtist and j for JacksonFive. The join results gets all JacksonFive singers a became solo artists, and it fetches their name and net worth columns. A join always shows the matching left and right table rows. However, you may want to only see unmatched rows from left table, right table, or both tables.  There are several types of database joins: An INNER JOIN selects only matching left and right table rows Compared to an inner join, there are 3 types of outer joins: LEFT JOIN- selects all left table rows, and only matching right table rows RIGHT JOIN- selects all right table rows, and only matching left table rows FULL JOIN - selects all left table rows, and all right table rows The result table of an outer join may contain unknown values that become set to NULL. When creating a join, the FROM clause uses the left table, and the JOIN clause uses the right table. Also, use the ON clause to specify the join columns from the table. Besides from inner and outer joins, relational databases also have a few other types of joins. An equijoin compares columns of two tables with the equality = operator. They are the most common type of joins, and all inner and outer joins are examples of equijoins. A non-equijoin compares columns without using the equality operator =, such as less than, less than or equal to, greater than, or greater than or equal to. In a self-join, a table that joins itself by comparing any of it’s columns that have comparable data types. If a foreign key and the referenced primary key are in the same table, a self-join can be used to compare key columns. If using a self-join, you may want to use an alias, which is a temporary name for your table or column. It makes dealing with SQL statements easier if you are comparing columns with the same name in different tables, or even within the same table in a self-join. A cross-join combines two tables without comparing any of the columns. The result is all possible combinations of rows from both tables. Subscribe to Appficial for more programming videos coming soon. Also, don't forget to click LIKE and comment on the video if it helped you out!