![]() Often, that’s probably not what we want to do. Since they have city_id set to null, they would have been filtered out. See, student number 2 is now included (compare with the inner join above). The right table (the word after join): sqlite> select * from student left join city on city.id = student.city_id The solution? Left join will make the system to join rowsįrom the left table (the word after from) even if there are no matches from The thing is, null equals to nothing, therefore a student with null city id The student is no longer there because Eve has null value for their city_id: sqlite> select * from student, city where city.id = student.city_id Let’s do the inner join using one of the syntaxes I explained above and notice Let’s set city for the student with id of 2 to null: sqlite> update student set city_id = null where id = 2 ![]() Sqlite> select * from student left join city on city.id = student.city_id Null values in the database, which is not this case: sqlite> select * from student inner join city on city.id = student.city_id Here is our list of students and their homeĬities: sqlite> select student.name,city.name from student, city where city.id = student.city_id īut how about left join and right join? Well, they only matter if you have Relational database systems I know will work the same. Which one to choose from when you want to do an inner join? It’s up to you, all Sqlite> select * from city inner join student on student.city_id = city.id Sqlite> select * from student inner join city on city.id = student.city_id Sqlite> select * from student, city where student.city_id = city.id Results: sqlite> select * from student, city where city.id = student.city_id To recap, the following are all inner join statements giving you the same Sqlite> select * from student inner join city on student.city_id = city.id Order, results are exactly the same: sqlite> select * from city inner join student on student.city_id = city.id The same goes for from/join clauses, it really does not matter which table goesįirst in the from or join statements, the only thing that changes is column Swapping columns in the where clause does not change the result either: sqlite> select * from student inner join city on student.city_id = city.id Now here is a thing, inner join is exactly that, but with different syntax: sqlite> select * from student inner join city on city.id = student.city_id Same result: sqlite> select * from student, city where student.city_id = city.id Speaking, you can swap the columns in the where clause only to get the very This is typically what we want! Because equality is commutative, technically Student’s city_id column: sqlite> select * from student, city where city.id = student.city_id Let’s filter out only those rows where city id equals It is possible to use where clause the same way as in other statements, to Result with different column order: sqlite> select * from city, student The order of tables in the select statement does not matter, it’s the same ![]() Of rows from one table multiplied by number from the other table: 2*3=6: sqlite> select * from student, city Returns one table, it has to do it that way. ![]() Nothing else than “all options possible”. Now, it’s possible to select rows from more than one table, in that caseĭatabase systems return what is technically called cartesian product which is We have three students from two cities in total, each student having an id ofĬity they are from: sqlite> select * from city Insert few rows: sqlite> insert into city (id, name) values (1, 'Prague'), (2, 'New York') Sqlite> create table student (id integer, name text, city_id integer) I will use the tiny SQLite3 database system, you can do this too: $ sqlite3 joins.sqlite3Ĭreate tables city and student: sqlite> create table city (id integer, name text) Imagine a database of student with a simple (1:N) relationship, a city studentsĪre from. I’ve got asked how SQL joins really work. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |