数据库和linq中的 join（连接）操作

sql中的连接

sql中的表连接有inner join，left join(left outer join),right join(right outer join),full join(full outer join),cross join

在此基础上我们能扩展出 left excluding join,right excluding join,full outer excluding join

注：left join是left outer join 的简写，即左连接和左外连接是一样的

首先定义两个比较经典的表

学生信息表和选课表

student

studentId	name	　　　　sex
1         	小明        	男
2         	小黄        	男
3         	小红        	女
4         	小杨        	男

course

studentId	courseName
1         	数学        
1         	语文        
1         	英语        
2         	数学        
2         	语文        
2         	英语        
3         	数学        
3         	语文        
3         	英语        
5         	数学        
5         	语文        
5         	英语        

这两张表其实并不规范，course的studentId其实是一个外键，对应student的studentId，所以course的studentId不应该有5，不过为了测试方便，暂且这么写

内连接（inner join）

select s.* ,c.courseName
from student s
inner join course c 
on s.studentId=c.studentId

结果

studentId	name	　　　　 sex	courseName
1         	小明        	男	数学        
1         	小明        	男	语文        
1         	小明        	男	英语        
2         	小黄        	男	数学        
2         	小黄        	男	语文        
2         	小黄        	男	英语        
3         	小红        	女	数学        
3         	小红        	女	语文        
3         	小红        	女	英语        

左连接（left join）　　

select s.* ,c.courseName
from student s
left join course c 
on s.studentId=c.studentId

结果

studentId	name	        sex	courseName
1         	小明        	男	数学        
1         	小明        	男	语文        
1         	小明        	男	英语        
2         	小黄        	男	数学        
2         	小黄        	男	语文        
2         	小黄        	男	英语        
3         	小红        	女	数学        
3         	小红        	女	语文        
3         	小红        	女	英语        
4         	小杨        	男	NULL

右连接

select s.* ,c.courseName
from student s
right join course c 
on s.studentId=c.studentId

结果

studentId    name     　　　　sex    　courseName
1             小明            男    　　数学        
1             小明            男    　　语文        
1             小明            男   　　 英语        
2             小黄            男    　　数学        
2             小黄            男    　　语文        
2             小黄            男    　　英语        
3             小红            女   　　 数学        
3             小红            女    　　语文        
3             小红            女    　　英语        
NULL    　　　NULL    　　　　 NULL   　 数学        
NULL    　　　NULL   　　　　　NULL    　语文        
NULL    　　　NULL    　　　　 NULL    　英语        

全连接

select s.* ,c.courseName
from student s
full join course c 
on s.studentId=c.studentId

结果

studentId    name    　　　　 sex   courseName
1             小明            男    数学        
1             小明            男    语文        
1             小明            男    英语        
2             小黄            男    数学        
2             小黄            男    语文        
2             小黄            男    英语        
3             小红            女    数学        
3             小红            女    语文        
3             小红            女    英语        
4             小杨            男    NULL
NULL    　　　 NULL    　　　　NULL  数学        
NULL    　　　 NULL    　　　　NULL  语文        
NULL    　　　 NULL    　　　　NULL  英语        

左不包含连接（left excluding join）

select s.* ,c.courseName 
from student s
left join course c 
on s.studentId=c.studentId
where c.studentId is null

结果

studentId	name	sex	courseName
4         	小杨     男	NULL

右不包含连接（right excluding join）

select s.* ,c.courseName 
from student s
right join course c 
on s.studentId=c.studentId
where s.studentId is null

结果

studentId	name	sex	courseName
NULL	　　　　 NULL	NULL	数学        
NULL	　　　　 NULL	NULL	语文        
NULL	　　　　 NULL	NULL	英语        

全不包含连接（Full outer excluding join）

select s.* ,c.courseName 
from student s
full join course c 
on s.studentId=c.studentId
where s.studentId is null or c.studentId is null

结果

studentId	name	sex	courseName
4         	小杨     男	NULL
NULL	　　　　 NULL	NULL	数学        
NULL	　　　 　NULL	NULL	语文        
NULL	　　　　 NULL	NULL	英语        

笛卡儿积（cross join）　

select s.* ,c.courseName 
from student s
cross join course c 

结果

studentId    name    sex    courseName
1             小明            男    数学        
1             小明            男    语文        
1             小明            男    英语        
1             小明            男    数学        
1             小明            男    语文        
1             小明            男    英语        
1             小明            男    数学        
1             小明            男    语文        
1             小明            男    英语        
1             小明            男    数学        
1             小明            男    语文        
1             小明            男    英语        
2             小黄            男    数学        
2             小黄            男    语文        
2             小黄            男    英语        
2             小黄            男    数学        
2             小黄            男    语文        
2             小黄            男    英语        
2             小黄            男    数学        
2             小黄            男    语文        
2             小黄            男    英语        
2             小黄            男    数学        
2             小黄            男    语文        
2             小黄            男    英语        
3             小红            女    数学        
3             小红            女    语文        
3             小红            女    英语        
3             小红            女    数学        
3             小红            女    语文        
3             小红            女    英语        
3             小红            女    数学        
3             小红            女    语文        
3             小红            女    英语        
3             小红            女    数学        
3             小红            女    语文        
3             小红            女    英语        
4             小杨            男    数学        
4             小杨            男    语文        
4             小杨            男    英语        
4             小杨            男    数学        
4             小杨            男    语文        
4             小杨            男    英语        
4             小杨            男    数学        
4             小杨            男    语文        
4             小杨            男    英语        
4             小杨            男    数学        
4             小杨            男    语文        
4             小杨            男    英语        

两个个经典sql问题的解法

一、取出没有选课的学生的信息

方法一：利用left excluding join

select s.* 
from student s
left join course c 
on s.studentId=c.studentId
where c.studentId is null

结果

studentId	name	sex
4         	小杨     男

方法二：利用exists

思路：先找到有选课的学生的信息然后通过exists或not exists来取出想要的数据

select * from student st 
where not exists(
	select s.* ,c.courseName
	from student s
	inner join course c 
	on s.studentId=c.studentId
	where st.studentId=s.studentId
)

结果跟方法一的一样

二、取出有选课的学生的信息

select * from student st 
where exists(
	select s.* ,c.courseName
	from student s
	inner join course c 
	on s.studentId=c.studentId
	where st.studentId=s.studentId
)

结果

studentId	name	　　　　 sex
1         	小明        	男
2         	小黄        	男
3         	小红        	女

Linq 中的连接

在linq中同样能实现上述sql的连接操作

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
namespace LinqJoinTest
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable student = GetStudent();
            DataTable course = GetCourse();
            Console.WriteLine("内连接");
            IEnumerable<ResultModel> result = InnerJoin(student, course);
            foreach(ResultModel item in result)
            {
                Console.WriteLine(string.Format("{0},{1},{2},{3}", item.id, item.name, item.sex, item.course));
            }
            Console.WriteLine("左连接");
            result = LeftJoin(student, course);
            foreach (ResultModel item in result)
            {
                Console.WriteLine(string.Format("{0},{1},{2},{3}", item.id, item.name, item.sex, item.course));
            }
            Console.WriteLine("右连接");
            result = RightJoin(student, course);
            foreach (ResultModel item in result)
            {
                Console.WriteLine(string.Format("{0},{1},{2},{3}", item.id, item.name, item.sex, item.course));
            }
            Console.WriteLine("全连接");
            result = AllJoin(student, course);
            foreach (ResultModel item in result)
            {
                Console.WriteLine(string.Format("{0},{1},{2},{3}", item.id, item.name, item.sex, item.course));
            }
            Console.WriteLine("左不包含连接");
            result = LeftOuterJoin(student, course);
            foreach (ResultModel item in result)
            {
                Console.WriteLine(string.Format("{0},{1},{2},{3}", item.id, item.name, item.sex, item.course));
            }
            Console.WriteLine("右不包含连接");
            result = RightOuterJoin(student, course);
            foreach (ResultModel item in result)
            {
                Console.WriteLine(string.Format("{0},{1},{2},{3}", item.id, item.name, item.sex, item.course));
            }
            Console.WriteLine("全不包含连接");
            result = AllOuterJoin(student, course);
            foreach (ResultModel item in result)
            {
                Console.WriteLine(string.Format("{0},{1},{2},{3}", item.id, item.name, item.sex, item.course));
            }
            Console.ReadKey();
        }

        public static DataTable GetStudent()
        {
            DataTable student = new DataTable();
            student.Columns.Add("studentId");
            student.Columns.Add("name");
            student.Columns.Add("sex");
            student.Rows.Add(new object[] { "1", "小明", "男" });
            student.Rows.Add(new object[] { "2", "小黄", "男" });
            student.Rows.Add(new object[] { "3", "小红", "女" });
            student.Rows.Add(new object[] { "4", "小杨", "男" });
            return student;
        }

        public static DataTable GetCourse()
        {
            DataTable course = new DataTable();
            course.Columns.Add("studentId");
            course.Columns.Add("courseName");
            course.Rows.Add(new object[] { "1", "数学" });
            course.Rows.Add(new object[] { "1", "英语" });
            course.Rows.Add(new object[] { "1", "语文" });
            course.Rows.Add(new object[] { "2", "数学" });
            course.Rows.Add(new object[] { "2", "英语" });
            course.Rows.Add(new object[] { "2", "语文" });
            course.Rows.Add(new object[] { "3", "数学" });
            course.Rows.Add(new object[] { "3", "英语" });
            course.Rows.Add(new object[] { "3", "语文" });
            course.Rows.Add(new object[] { "5", "数学" });
            course.Rows.Add(new object[] { "5", "英语" });
            course.Rows.Add(new object[] { "5", "语文" });
            return course;
        }

        /// <summary>
        /// 内连接
        /// </summary>
        /// <param name="student"></param>
        /// <param name="course"></param>
        /// <returns></returns>
        public static IEnumerable<ResultModel> InnerJoin(DataTable student, DataTable course)
        {
            //Lambda表达式
            var result = from s in student.Select()
                         join c in course.Select() on s["studentId"].ToString() equals c["studentId"].ToString()
                         select new ResultModel
                         {
                             id = s["studentId"].ToString(),
                             name = s["name"].ToString(),
                             sex = s["sex"].ToString(),
                             course = c["courseName"].ToString()
                         };
            //查询表达式语法
            result = student.Select()
                .Join(course.Select(), s => s["studentId"].ToString(), c => c["studentId"].ToString(),
                (s, c) => new ResultModel
                {
                    id = s["studentId"].ToString(),
                    name = s["name"].ToString(),
                    sex = s["sex"].ToString(),
                    course = c["courseName"].ToString()

                });
            return result;
        }

        /// <summary>
        /// 左连接（左外连接） linq中只有左连接，右连接只要把数据集合顺序倒转就行了
        /// </summary>
        /// <param name="student"></param>
        /// <param name="course"></param>
        /// <returns></returns>
        public static IEnumerable<ResultModel> LeftJoin(DataTable student, DataTable course)
        {
            //Lambda表达式
            var result = from s in student.Select()
                         join c in course.Select() on s["studentId"].ToString() equals c["studentId"].ToString() into temple
                         from t in temple.DefaultIfEmpty()
                         select new ResultModel
                         {
                             id = s["studentId"].ToString(),
                             name = s["name"].ToString(),
                             sex = s["sex"].ToString(),
                             course = t==null?"Null":t["courseName"].ToString()
                         };
            //查询表达式语法
            result = student.Select().GroupJoin(course.Select(), s => s["studentId"].ToString(), c => c["studentId"].ToString(),
                (s, c) => new { s, c }).SelectMany(g => g.c.DefaultIfEmpty(), (item, c) => new ResultModel
                {
                    id = item.s["studentId"].ToString(),
                    name = item.s["name"].ToString(),
                    sex = item.s["sex"].ToString(),
                    course = c == null ? "Null" : c["courseName"].ToString()

                });
            return result;
        }

        /// <summary>
        /// 右连接（右外连接）
        /// </summary>
        /// <param name="student"></param>
        /// <param name="course"></param>
        /// <returns></returns>
        public static IEnumerable<ResultModel> RightJoin(DataTable student, DataTable course)
        {
            //Lambda表达式
            var result = from c in course.Select()
                         join s in student.Select() on c["studentId"].ToString() equals s["studentId"].ToString() into temple
                         from t in temple.DefaultIfEmpty()
                         select new ResultModel
                         {
                             id = t == null ? "Null" : t["studentId"].ToString(),
                             name = t == null ? "Null" : t["name"].ToString(),
                             sex = t == null ? "Null" : t["sex"].ToString(),
                             course = c["courseName"].ToString()
                         };
            //查询表达式语法
            result = course.Select().GroupJoin(student.Select(), s => s["studentId"].ToString(), c => c["studentId"].ToString(),
                (s, c) => new { s, c }).SelectMany(g => g.c.DefaultIfEmpty(), (item, c) => new ResultModel
                {
                    id = c == null ? "Null" : c["studentId"].ToString(),
                    name = c == null ? "Null" : c["name"].ToString(),
                    sex = c == null ? "Null" : c["sex"].ToString(),
                    course =item.s["courseName"].ToString()

                });
            return result;
        }

        /// <summary>
        /// 全连接（全外连接）
        /// </summary>
        /// <param name="student"></param>
        /// <param name="course"></param>
        /// <returns></returns>
        public static IEnumerable<ResultModel> AllJoin(DataTable student, DataTable course)
        {
            IEnumerable<ResultModel> left = LeftJoin(student, course);
            IEnumerable<ResultModel> right = RightJoin(student, course);

            //比较器
            IEqualityComparer<ResultModel> ec = new EntityComparer();
            return left.Union(right, ec);
        } 

        /// <summary>
        /// 左不包含连接
        /// </summary>
        /// <param name="student"></param>
        /// <param name="course"></param>
        /// <returns></returns>
        public static IEnumerable<ResultModel> LeftOuterJoin(DataTable student, DataTable course)
        {
            //Lambda表达式
            var result = from s in student.Select()
                         join c in course.Select() on s["studentId"].ToString() equals c["studentId"].ToString() into temple
                         from t in temple.DefaultIfEmpty()
                         where t==null
                         select new ResultModel
                         {
                             id = s["studentId"].ToString(),
                             name = s["name"].ToString(),
                             sex = s["sex"].ToString(),
                             course ="Null"
                         };
            //查询表达式语法
            result = student.Select().GroupJoin(course.Select(), s => s["studentId"].ToString(), c => c["studentId"].ToString(),
                (s, c) => new { s, c })
                .SelectMany(g => g.c.DefaultIfEmpty(), (item, c) => new { item,c}).Where(item => item.c== null)
                .Select(item=>new ResultModel
                {
                    id = item.item.s["studentId"].ToString(),
                    name = item.item.s["name"].ToString(),
                    sex = item.item.s["sex"].ToString(),
                    course ="Null"
                });
            return result;
        }

        /// <summary>
        /// 右不包含连接
        /// </summary>
        /// <param name="student"></param>
        /// <param name="course"></param>
        /// <returns></returns>
        public static IEnumerable<ResultModel> RightOuterJoin(DataTable student, DataTable course)
        {
            //Lambda表达式
            var result = from c in course.Select()
                         join s in student.Select() on c["studentId"].ToString() equals s["studentId"].ToString() into temple
                         from t in temple.DefaultIfEmpty()
                         where t==null
                         select new ResultModel
                         {
                             id = "Null",
                             name = "Null",
                             sex = "Null",
                             course = c["courseName"].ToString()
                         };
            //查询表达式语法
            result = course.Select().GroupJoin(student.Select(), s => s["studentId"].ToString(), c => c["studentId"].ToString(),
                (s, c) => new { s, c }).SelectMany(g => g.c.DefaultIfEmpty(), (item, c) => new { item, c }).Where(item=>item.c==null)
                .Select(item => new ResultModel
                {
                    id ="Null",
                    name ="Null",
                    sex = "Null" ,
                    course = item.item.s["courseName"].ToString()

                });
            return result;
        }

        /// <summary>
        /// 全不包含连接
        /// </summary>
        /// <param name="student"></param>
        /// <param name="course"></param>
        /// <returns></returns>
        public static IEnumerable<ResultModel> AllOuterJoin(DataTable student, DataTable course)
        {
            IEnumerable<ResultModel> left = LeftOuterJoin(student, course);
            IEnumerable<ResultModel> right = RightOuterJoin(student, course);

            return left.Union(right);
        }

        /// <summary>
        /// 交叉连接（笛卡尔积）
        /// </summary>
        /// <param name="student"></param>
        /// <param name="course"></param>
        /// <returns></returns>
        public static IEnumerable<ResultModel> CrossJoin(DataTable student, DataTable course)
        {
            //Lambda表达式
            var result = from s in student.Select()
                         from c in course.Select() 
                         select new ResultModel
                         {
                             id = s["studentId"].ToString(),
                             name = s["name"].ToString(),
                             sex = s["sex"].ToString(),
                             course = c["courseName"].ToString()
                         };
            //查询表达式语法
            result = student.Select()
                .SelectMany(c=>course.Select(),
                (s, c) => new ResultModel
                {
                    id = s["studentId"].ToString(),
                    name = s["name"].ToString(),
                    sex = s["sex"].ToString(),
                    course = c["courseName"].ToString()

                });
            return result;
        }

    }

    public class ResultModel
    {
        public string id { get; set; }
        public string name { get; set; }
        public string sex { get; set; }
        public string course { get; set; }
    }

    public class EntityComparer : IEqualityComparer<ResultModel>
    {
        public bool Equals(ResultModel a, ResultModel b)
        {
            if (Object.ReferenceEquals(a, b)) return true;
            if (Object.ReferenceEquals(a, null) || Object.ReferenceEquals(b, null))
                return false;
            return a.id == b.id && a.name == b.name && a.sex == b.sex&&a.course==b.course;
        }

        public int GetHashCode(ResultModel a)
        {
            if (Object.ReferenceEquals(a, null)) return 0;
            int hashId = a.id == null ? 0 : a.id.GetHashCode();
            int hashName = a.name == null ? 0 : a.id.GetHashCode();
            int hashSex = a.sex == null ? 0 : a.sex.GetHashCode();
            int hashCourse = a.course == null ? 0 : a.course.GetHashCode();
            return hashId ^ hashName ^ hashSex ^ hashCourse;
        }
    }  

}