JOIN Statement

The SQLDatabase.Net library support LEFT JOIN only not the right join, however right join can be achieved by changing the table order.

A SQL JOIN combines records from two or more tables or views.
A JOIN locates related column values within multiple tables or views.
A query can contain zero, one, or multiple JOIN operations.
INNER JOIN is the same as JOIN, the keyword INNER is optional.

CREATE TABLE IF NOT EXISTS Departments
(
 DepartmentId Integer Primary Key AutoIncrement
,DepartmentName TEXT
);


INSERT INTO Departments(DepartmentName) VALUES('Administration');
INSERT INTO Departments(DepartmentName) VALUES('Sales & Marketing');
INSERT INTO Departments(DepartmentName) VALUES('HR');
INSERT INTO Departments(DepartmentName) VALUES('Accounts');

DROP TABLE IF EXISTS EmployeeMaster;
CREATE TABLE IF NOT EXISTS EmployeeMaster
(
EmployeeId Integer Primary Key AutoIncrement,
FirstName Text,
LastName Text,
DepartmentId Integer,
ReportsTo Integer,
FOREIGN KEY (DepartmentId) REFERENCES Departments (DepartmentId)
);

INSERT INTO EmployeeMaster values (1,'John','Dekota', 2,7);
INSERT INTO EmployeeMaster values (2,'Mary','Denware',3,2);
INSERT INTO EmployeeMaster values (3,'Scott','Hamilton',2,7);
INSERT INTO EmployeeMaster values (4,'Guru','Manna',3,2);
INSERT INTO EmployeeMaster values (5,'Robert','Olipo',1,3);
INSERT INTO EmployeeMaster values (6,'Mark','Netcal',2,7);
INSERT INTO EmployeeMaster values (7,'Peter','adam',3,2);
INSERT INTO EmployeeMaster values (8,'Martin','Jerico',2,7);
INSERT INTO EmployeeMaster values (9,'Davis','Ocee',1,3);

The JOIN keyword returns records from the left table (Departments) and matching records in the right table (EmployeeMaster). join syntax : Departments d JOIN EmployeeMaster e.

SELECT d.DepartmentId,DepartmentName, EmployeeId, FirstName, LastName
FROM 
Departments d 
JOIN
EmployeeMaster e 
ON d.DepartmentId = e.DepartmentId;