For results from related tables, learn how you may combine results from more than one table by using the JOIN clause in your query.
- [Narrator] It's the nature of a relational database that some tables contain information related to other tables. Using a joined query, SQL may easily perform queries on related data from multiple tables. Typically a unique id column is used to create relationships. All modern database systems support automatic generation of these id columns and they work well for creating and managing simple or complex relationships between tables.
When you need a result that includes related rows from multiple tables, you'll need to use a joined query. You can visualize a joined query as a Venn diagram, where each of your tables are represented by intersecting shapes. The intersection of the shapes, where the tables overlap, are the rows where a condition is met. Id columns are often used for this purpose, where the condition to be met is matching the ids of rows. The simplest and most common form of a join is the inner join.
This is the default and it's the join you get when you use the join keyword by itself. The result of an inner join will include rows frow both tables where the join condition is met. An outer join is less common, but still important to understand. The left outer join includes rows where the condition is met plus all the rows from the table on the left where the condition is not met. Likewise, a right outer join includes all the rows from the table on the right.
The right outer join is considered a special case and many databases don't support right joins. Generally, a right join can be rewritten as a left join by simply changing the order of the tables in the query. A full outer join combines the effects of the left and right joins. Many databases do not implement full outer join, although the effect can be achieved with other features. There are many variations of these basic joins implemented in different ways by the different database management system vendors.
For most purposes, you'll just need to understand these basic concepts. For more specific implementation details, you should consult the documentation for your database system. You'll see some examples in the rest of this chapter.
- Understanding SQL terminology and syntax
- Creating new tables
- Inserting and updating data
- Writing basic SQL queries
- Sorting and filtering
- Accessing related tables with JOIN
- Working with strings
- Understanding numeric types
- Using aggregate functions and transactions
- Automating data with triggers
- Creating views
- Defining functions in PHP
Skill Level Beginner
2. SQL Overview
3. Fundamental Concepts
7. Dates and Times
Dates and times1m 52s
11. Views and Subselects
12. Defined Functions
13. A Simple CRUD Application
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.