Learn SQL
JOINS - INNER JOIN
35.1
Lesson

Context for JOINS

In real world applications, the databases follow principles that segregate data of different kinds into different tables inside a database. Any relationship that needs to be setup between two or more tables is made through linking their primary references appropriately.

In such cases, where we have to select, filter, order or view data which involves operations over multiple tables, SQL provides JOINS, which is a feature we will use in this and forthcoming chapters.

Here is a small description of the tables and the relationships amongst them at this point.

  1. students
    • A list of students
    • Each student is enrolled in a course. The data for the course is mentioned in the column courseId which matches the id from courses table. Some values from courseId may not be available, and some may contain an id that does not exist in courses table
    • A student can have a student mentor who is, another student. The data for the student mentor is stored in the column studentMentorId, which matches the id from students table itself. Not all students have a student mentor, so some values in studentMentorId may be null.
    • columns:
      1. id (integer: id of the student)
      2. name (text: name of the student)
      3. age (integer: age of the student)
      4. grade (integer: grade of the student)
      5. marks (integer: marks scored by the student)
      6. courseId (integer: id of the course the student is enrolled in)
      7. studentMentorId (integer: id of the student mentor)

  2. courses
    • A list of courses
    • columns:
      1. id (integer: id of the course)
      2. name (text: name of the course)
      3. abbreviatedName (text: abbreviated name of the course)

  3. books
    • A list of books.
    • Each book is written by an author. The data for the author is mentioned in the column authorId which matches the id from authors table. Some values from authorId may not be available, and some may contain an id that does not exist in authors table
    • Each book also belongs to a specific course. The data for the course is mentioned in the column courseId which matches the id from courses table. Some values from courseId may not be available, and some may contain an id that does not exist in courses table
    • Some books are part of a series. So, these books have reference to the next book in the series, stored in the column nextVolumeId, which are in turn id of the next volume of books in the series.
    • columns:
      1. id (integer: id of the book)
      2. name (text: name of the book)
      3. price (integer: price of the book)
      4. publishedIn (date: year the book was published in)
      5. publisherName (text: name of the publisher)
      6. courseId (integer: id of the course the book belongs to)
      7. authorId (integer: id of the author of the book)
      8. nextVolumeId (integer: id of the book which is the next volume in series)

  4. authors
    • A simple list of book authors.
    • columns:
      1. id (integer: id of the author)
      2. name (text: name of the author)
      3. gender (text: gender of the author)
      4. codeName (text: code name of the author)

Go ahead, have a look at the tables present in the editor below before we get into the next lessons.