![]() ![]() ![]() You can easily notice, that we have 2 more rows, compared to the result of the INNER JOIN query. We’ll use the same INNER JOIN query and just replace the word INNER with LEFT. The result of LEFT JOIN shall be the same as the result of INNER JOIN + we’ll have rows, from the “left” table, ![]() In databases, LEFT JOIN does exactly that. No matter what the motivation behind that desire is, we should be technically able to do that.Īnd we are. This could be part of some control, or maybe just counting cases, etc. For example, you simply want to see in the result that these countries don’t have related records in another table. In some cases, we want to have even these records in our results. This shall prove crucial when comparing INNER JOIN vs LEFT JOIN. ![]() I’ll repeat this – “We don’t have 2 countries on the list (Spain and Russia) because they don’t have any related city in the city table“. We don’t have 2 countries in the list (Spain and Russia), because they don’t have any related city in the Now, let’s comment on what queries actually returned:Īll pairs of countries and cities that are related (via foreign key) If you want to use other JOINs later (LEFT or RIGHT), you couldn’t do that (easily) unless you’ve used INNER You can easily see if you omitted the JOIN condition or not Readability is much better because the table used and related JOIN condition are in the same line. While both queries are well-written, I would suggest that you always use INNER JOIN instead of listing tables and joining them in the WHERE part of the query. In the second query, we have only one table in the FROM part of the query (FROM country) and then we have the second table and the JOIN condition in the JOIN part of the query (INNER JOIN city ON untry_id = country.id). In case we forgot to write down this join condition, we would have the Cartesian product of both tables. In the first query, we listed all tables we use in the FROM part of the query (FROM country, city) and then went with the join condition in the WHERE part of the query (WHERE untry_id = country.id). Both ways are correct, and you can use any of them. This is not by accident but the result of the fact that this is the same query written in two different ways. The result they return is presented on the picture below:īoth queries return exactly the same result. Therefore, they’re used in the payment table as a foreign key.INNER JOIN city ON city. Note that the student_id and course_code columns form a primary key in the enrollment table. In the next condition, we get the course_code column from the enrollment table and course_code from the payment table. In the first part, we use the student_id column from the enrollment table and student_id from the payment table. P.course_code=e.course_code AND p.student_id=e.student_id How can we join the tables with these compound keys?Įasy! We just need to use a JOIN clause with more than one condition by using the AND operator after the first condition. In the second table ( payment), we have columns that are a foreign compound key ( student_id and course_code). In one joined table (in our example, enrollment), we have a primary key built from two columns ( student_id and course_code). If you’d like to get data stored in tables joined by a compound key that’s a primary key in one table and a foreign key in another table, simply use a join condition on multiple columns. Let’s show each student’s name, course code, and payment status and amount. The payment table has data in the following columns: foreign key ( student_id and course_code, the primary keys of the enrollment table), status, and amount. The enrollment table has data in the following columns: primary key ( student_id and course_code), is_active, and start_date. The student table has data in the following columns: id (primary key), first_name, and last_name. Our database has three tables named student, enrollment, and payment. You want to join tables on multiple columns by using a primary compound key in one table and a foreign compound key in another. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |