How do I get rows from one table that do not exist in another table

Question

I am building a Spring boot application with the following table DDLs

CREATE TABLE IF NOT EXISTS QUESTIONS(
   question_id bigint auto_increment,
   question_text varchar(256),
   answer boolean, primary key(question_id)
);

CREATE INDEX IF NOT EXISTS question_id_index on Questions(question_id);

and

CREATE TABLE IF NOT EXISTS QuizQuestions(
    id bigint auto_increment,
    quiz_id varchar(10),
    question_id bigint,
    FOREIGN KEY(quiz_id) REFERENCES Quiz(quiz_id),
    FOREIGN KEY(question_id) REFERENCES Questions(question_id)
)

I need to write a sql query such that

1) if there is at least one row which exists in QuizQuestions table for quiz_id , then I need to return all the question_id entries from Questions table that don't exist in QuizQuestions table.

2) If there aren't any rows for a specific quiz id in QuizQuestions table, then I want to return all rows from Questions table.

My sample data is as follows

INSERT INTO Questions(question_id, question_text, answer) VALUES (1, 'Is 2016 a leap year?', TRUE);
INSERT INTO Questions(question_id, question_text, answer) VALUES(2, 'Is Sunday a weekday?', FALSE);
INSERT INTO Questions(question_id, question_text, answer) VALUES(3, 'Is August the 8th month?', TRUE);
INSERT INTO Questions(question_id, question_text, answer) VALUES(4, 'Is July 25 independence day of United States of America?', FALSE);
INSERT INTO Questions(question_id, question_text, answer) VALUES(5, 'Does January have 31 days in a month?', TRUE);
INSERT INTO Questions(question_id, question_text, answer) VALUES(6, 'Does UK have a monarchy?', TRUE);
INSERT INTO Questions(question_id, question_text, answer) VALUES(7, 'Does Lebron play football?', FALSE);
INSERT INTO Questions(question_id, question_text, answer) VALUES(8, 'Is Los Angeles in New York state?', FALSE);
INSERT INTO Questions(question_id, question_text, answer) VALUES(9, 'Is Canada to the north of United States?', TRUE);
INSERT INTO Questions(question_id, question_text, answer) VALUES(10, 'Does November have 30 days in a month?', TRUE);

and

INSERT INTO QuizQuestions(quiz_id, question_id) VALUES('ABCDE12345', 5);

So if I query for quiz_id = ABCDE12345, I want to return all rows from Questions except the entry for question_id=5 because that already exists in QuizQuestions table.

If I were to query for any other quiz id that does not exist in QuizQuestions table, then I want to return all entries from Question table. This is amy sample query

SELECT q.question_id, q.question_text, q.answer FROM Questions q
LEFT JOIN QuizQuestions qq ON q.question_id = qq.question_id WHERE 
qq.quiz_id = ?

and

SELECT q.question_id, q.question_text, q.answer FROM Questions q 
LEFT JOIN QuizQuestions qq ON 
q.question_id = qq.question_id WHERE q.question_id NOT IN 
(SELECT qq.question_id FROM Questions WHERE qq.quiz_id = ?)

I have tried every possible query that I can think of, but I have not been able to find an output.

UPDATE

This is the code that I am using to query the database. The collection output is always empty.

public QuizQuestion fetchUniqueQuizQuestion(String quizId) {
    StringBuilder sb = new StringBuilder();
    sb.append("SELECT q.question_id, q.question_text, q.answer FROM Questions q ");
    sb.append("WHERE NOT EXISTS (SELECT 1 FROM QuizQuestions WHERE question_id = q.question_id ");
    sb.append(" AND quiz_id = ?) ");
    sb.append("OR NOT EXISTS (SELECT 1 FROM QuizQuestions WHERE quiz_id = ?)");
    List<QuizQuestion> questions = 
        this.jdbcTemplate.query(
            sb.toString(), new Object[] {quizId, quizId}, new RowMapper<QuizQuestion>() {

          @Override
          public QuizQuestion mapRow(ResultSet rs, int rowNum) throws SQLException {
            QuizQuestion qq = new QuizQuestion();
            qq.setQuestionId(rs.getInt("question_id"));
            qq.setQuizId(quizId);
            qq.setAnswer(rs.getBoolean("answer"));
            qq.setDescription(rs.getString("question_text"));
            return qq;
          }
        });
    Collections.shuffle(questions);
    return questions.isEmpty() ? null : questions.get(0);
  }

Answer 1

Something like:

SELECT q.question_id, q.question_text, q.answer 
FROM Questions q
WHERE NOT EXISTS 
    (
        SELECT 1 
        FROM QuizQuestions 
        WHERE question_id = q.question_id
            AND
            quiz_id = 'the_quiz_id'
    )
    OR 
    NOT EXISTS
    (
        SELECT 1 
        FROM QuizQuestions 
        WHERE quiz_id = 'the_quiz_id'
    )