INNER JOIN that combines 4 tables: student, teacher, instrument and scheduledLessons

SELECT stu.fName AS 'Student Name', teach.fName AS 'Teacher Name',
         less.time AS 'Scheduled Time', inst.name AS 'Instrument'
        FROM student stu
        JOIN scheduledLessons less
        ON stu.id_student = less.id_student
        JOIN teacher teach
        ON less.id_teacher = teach.id_teacher
        JOIN instrument inst
        ON less.id_instrument = inst.id_instrument
        ORDER BY less.time

Left Outer JOIN that shows all students even those with no instrument

SELECT stu.id_student, stu.fName AS 'First Name',
        stu.lName AS 'Last Name', inst.name AS 'Instrument', inst.manufacturer
        FROM student stu
        LEFT JOIN instrument inst
        ON stu.id_instrument = inst.id_instrument

Right Outer JOIN that shows all instruments even those with no available teachers

SELECT teach.id_teacher, teach.fName AS 'First Name',
        teach.lName AS 'Last Name', inst.name AS 'Instrument', inst.manufacturer
        FROM teacher teach
        RIGHT JOIN instrument inst
        ON teach.id_instrument = inst.id_instrument

An inner join shows what are the common elements between your database tables.
In my example, it is useful to show which teachers and students are scheduled together for music lessons. This will not show any student or teacher that is not currently scheduled for a lesson.
Additionally, I also joined a 4th table to show the instrument name as the type of instrument is what is used to pair a student with a teacher.

A Left Outer Join will show you all values for the first table selected, and only the data from the other tables with a matching value.
That matching value is whatever field was chosen to join on. In my example, it is the instrument ID.
So, my join is useful for identifying which students are currently not assigned an instrument. Showing they might need help choosing before they are ready to be assigned a teacher.
However, it will not show any instruments that are not assigned to at least 1 student.

The Right Outer Join is the same as the left except it shows all values for the 2nd tables and only matching values from the first.
In my example, it is useful for showing what instruments do not currently have a teacher assigned.
This might indicate to the business they need to hire a teacher for that instrument, especially if they have students waiting for those lessons.