Right and Left Join

The library supports only left joins and due to it's embedded nature and dynamic sql building the right join can be achieved by switching tables.

LEFT JOIN

The LEFT JOIN keyword returns all records from the left table, and the matched records from the right table. If there is no match, the result is NULL or Empty from the right side.

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
);

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 LEFT JOIN keyword returns all records from the left table (Departments), even if there are no matches in the right table (EmployeeMaster).

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

LEFT OUTER JOIN

There is no difference between left join and left outer join, some database system use Left OUTER JOIN syntax, sqldatabase.net also support this optional keyword of outer. The results will be same.

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

RIGHT JOIN

The SQLDatabase.Net library does not support Right Join keyword. However the right join can be achieved easily by switching the table from left to right as in the example below where instead of (Departments) table being on left it is on the right side.

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