Mysql – Design database –

SELECT ...
  FROM users u
  LEFT JOIN userroles ur ON ur.user_id = u.user_id AND ur.role_id = 2
 WHERE ur.role_id IS NULL
 ORDER BY u.user_id ASC;

Examine all rows of user, and their matching row from userroles with a role_is = 2 if it exists, eliminating the rows where it does indeed exist by requiring the role_id in the (non-existent) row to be null... which, since the row does not exist, is indeed null.

Alternately,

SELECT ...
  FROM users u
 WHERE NOT EXISTS (SELECT * FROM userroles ur WHERE ur.user_id = u.user_id AND ur.role_id = 2)
 ORDER BY u.user_id ASC;

Note that the SELECT * in the subquery doesn't actually select everything, it's just a way to express the test of whether such a row exists.

I would begin with a little tweak:

In your current design, there is no need to have "id" column on vocabularylists table. I will write it from top of my head, so apologies in advance for any syntactic mistakes. :)

First change:

CREATE TABLE IF NOT EXISTS `vocabularylists` (
  `name` varchar(20) UNIQUE NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);

This way, you will have numeric ID and easily modifiable name of the vocabulary. This will make occasional renaming of vocabulary list a bit easier. But, of course, you can use only name columnt and totally strip out the id.

Second change: You need to connect the vocabularies to vocabularylists with a foreign key constraint. The vocabularies table will look like this:

CREATE TABLE IF NOT EXISTS `vocabularies` (
    `vocabulary` varchar(20) NOT NULL,
    `translation` varchar(20) NOT NULL,
    `list_id` int(11),
    FOREIGN KEY (list_id) REFERENCES vocabularylists(id) ON DELETE CASCADE
);

By creating foreign key, you ensure so-called referential integrity: from now on, your database cannot store vocabulaies that do not belong to a vocabulary list. Also (as ensured by ON DELETE CASCADE) any deletes on vocabularylists automaticcaly result to deletion of corresponding entries on vocabularies.

Third change:

Querying the vocabularies table will be slow. Put indexes on your tables:

CREATE TABLE IF NOT EXISTS `vocabularylists` (
  `name` varchar(20) UNIQUE NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  INDEX `name_idx` (`name`)
);
CREATE TABLE IF NOT EXISTS `vocabularies` (
    `vocabulary` varchar(20) NOT NULL,
    `translation` varchar(20) NOT NULL,
    `list_id` int(11),
    FOREIGN KEY (`list_id`) REFERENCES vocabularylists(`id`) ON DELETE CASCADE,
    INDEX `vocabulary_idx` (`vocabulary`),
    INDEX `translation_idx` (`translation`)
);

This will give you much better performance.

A hint:

You have a very simple structure defined here. With little data, there will be no significant problems with performance. But the only thing you really need - looking at those two db tables - is a key-value store. So instead of a database, you could easily use a plain/CSV/... file which will be loaded on application startup into some in-memory store (at the beginning a hashmap, then something like Redis maybe).

Also a word in one language can have multiple meanings in another. Your database - as it is - cannot store those things. I don't know if it is desired behavior but it will be limiting you in case you are extending your application.