Yes. See the Join (SQL) > Outer join > Full outer join article on Wikipedia. SELECT employee.*, department.* FROM employee LEFT JOIN department ON employee.DepartmentID = department.DepartmentID UNION ALL SELECT employee.*, department.* FROM department LEFT JOIN employee ON employee.DepartmentID = department.DepartmentID WHERE employee.DepartmentID IS NULL
SELECT COALESCE(t1.id, t2.id) as id, t1.value1, t2.value2 FROM table1 t1 FULL JOIN table2 t2 ON t1.id = t2.id;
I think I have the answer here, which is not as elegant as I’d hoped, but it should do the trick: var studentIDs = StudentClasses.Select(sc => sc.StudentID) .Union(StudentTeachers.Select(st => st.StudentID); //.Distinct(); — Distinct not necessary after Union var q = from id in studentIDs join sc in StudentClasses on id equals sc.StudentID into jsc from … Read more
SELECT A.column2 , B.column2 , C.column2 FROM ( (SELECT month, column2 FROM table1) A FULL OUTER JOIN (SELECT month, column2 FROM table2) B on A.month= B.month FULL OUTER JOIN (SELECT month, column2 FROM table3) C on ISNULL(A.month, B.month) = C.month )
Yes, see the example on Wikipedia. SELECT employee.*, department.* FROM employee LEFT JOIN department ON employee.DepartmentID = department.DepartmentID UNION ALL SELECT employee.*, department.* FROM department LEFT JOIN employee ON employee.DepartmentID = department.DepartmentID WHERE employee.DepartmentID IS NULL
Update 1: providing a truly generalized extension method FullOuterJoin Update 2: optionally accepting a custom IEqualityComparer for the key type Update 3: this implementation has recently become part of MoreLinq – Thanks guys! Edit Added FullOuterGroupJoin (ideone). I reused the GetOuter<> implementation, making this a fraction less performant than it could be, but I’m aiming … Read more
A cross join produces a cartesian product between the two tables, returning all possible combinations of all rows. It has no on clause because you’re just joining everything to everything. A full outer join is a combination of a left outer and right outer join. It returns all rows in both tables that match the … Read more
You don’t have full joins in MySQL, but you can sure emulate them. For a code sample transcribed from this Stack Overflow question you have: With two tables t1, t2: SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id UNION SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.id The query above … Read more