MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Users
(
    UserID int NOT NULL primary key
    , UserName varchar(100) NOT NULL
);

CREATE TABLE Roles
(
    RoleID int NOT NULL primary key
    , RoleName varchar(50) NOT NULL
);

CREATE TABLE UserRoles
(
    UserID int NOT NULL
    , RoleID int NOT NULL
    , FOREIGN KEY UserRolesUserIDFK (UserID) REFERENCES Users (UserID)
    , FOREIGN KEY UserRolesRoleIDFK (RoleID) REFERENCES Roles (RoleID)
    , PRIMARY KEY (UserID, RoleID)
);
INSERT INTO Users (UserID, UserName)
VALUES (1, 'Sarah')
     , (2, 'Hannah');
INSERT INTO Roles (RoleID, RoleName)
VALUES (1, 'Administrator')
     , (2, 'User');
INSERT INTO UserRoles (UserID, RoleID)
VALUES (1, 2)
     , (2, 1)
     , (2, 2);
SELECT u.UserName
    , r.RoleName
FROM Users u
    INNER JOIN UserRoles ur ON u.UserID = ur.UserID
    INNER JOIN Roles r ON ur.RoleID = r.RoleID
ORDER BY u.UserName;


UserName
RoleName


Hannah
Administrator

Hannah
User

Sarah
User


SELECT u.UserName
    , (
        SELECT GROUP_CONCAT(r.RoleName SEPARATOR ', ') 
        FROM Roles r 
            INNER JOIN UserRoles ur ON r.RoleID = ur.RoleID
        WHERE ur.UserID = u.UserID) AS Roles
    , (
        SELECT COUNT(1)
        FROM UserRoles ur
        WHERE ur.UserID = u.UserID) AS CountOfRoles
FROM Users u
ORDER BY u.UserName;


UserName
Roles
CountOfRoles


Hannah
Administrator, User
2

Sarah
User
1


SELECT u.UserName
FROM Users u
    INNER JOIN UserRoles ur ON u.UserID = ur.UserID
    INNER JOIN Roles r ON ur.RoleID = r.RoleID
WHERE r.RoleName = 'User'
ORDER BY u.UserName;


UserName


Hannah

Sarah


SELECT u.UserName
FROM Users u
WHERE NOT EXISTS (
    SELECT 1
    FROM UserRoles ur
        INNER JOIN Roles r ON ur.RoleID = r.RoleID
    WHERE r.RoleName = 'Administrator'
        AND ur.UserID = u.UserID
    );


UserName


Sarah