数据库和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

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

course

?
1
2
3
4
5
6
7
8
9
10
11
12
13
studentId   courseName
1           数学       
1           语文       
1           英语       
2           数学       
2           语文       
2           英语       
3           数学       
3           语文       
3           英语       
5           数学       
5           语文       
5           英语       

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

 

内连接（inner join）

?
1
2
3
4
select s.* ,c.courseName
from student s
inner join course c
on s.studentId=c.studentId

结果

?
1
2
3
4
5
6
7
8
9
10
studentId   name    　　　　 sex    courseName
1           小明          男   数学       
1           小明          男   语文       
1           小明          男   英语       
2           小黄          男   数学       
2           小黄          男   语文       
2           小黄          男   英语       
3           小红          女   数学       
3           小红          女   语文       
3           小红          女   英语       

左连接（left join）　　

?
1
2
3
4
select s.* ,c.courseName
from student s
left join course c
on s.studentId=c.studentId

结果

?
1
2
3
4
5
6
7
8
9
10
11
studentId   name            sex courseName
1           小明          男   数学       
1           小明          男   语文       
1           小明          男   英语       
2           小黄          男   数学       
2           小黄          男   语文       
2           小黄          男   英语       
3           小红          女   数学       
3           小红          女   语文       
3           小红          女   英语       
4           小杨          男   NULL

右连接

?
1
2
3
4
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    　英语
复制代码

全连接

?
1
2
3
4
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

结果

?
1
2
studentId   name    sex courseName
4           小杨     男    NULL

右不包含连接（right excluding join）

?
1
2
3
4
5
select s.* ,c.courseName
from student s
right join course c
on s.studentId=c.studentId
where s.studentId is null

结果

?
1
2
3
4
studentId   name    sex courseName
NULL    　　　　 NULL   NULL    数学       
NULL    　　　　 NULL   NULL    语文       
NULL    　　　　 NULL   NULL    英语       

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

?
1
2
3
4
5
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

结果

?
1
2
3
4
5
studentId   name    sex courseName
4           小杨     男    NULL
NULL    　　　　 NULL   NULL    数学       
NULL    　　　 　NULL   NULL    语文       
NULL    　　　　 NULL   NULL    英语       

笛卡儿积（cross join）　

?
1
2
3
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             小杨            男    英语
View Code
复制代码

 

两个个经典sql问题的解法

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

方法一：利用left excluding join

?
1
2
3
4
5
select s.*
from student s
left join course c
on s.studentId=c.studentId
where c.studentId is null

结果

?
1
2
studentId   name    sex
4           小杨     男

方法二：利用exists

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

?
1
2
3
4
5
6
7
8
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
)

结果跟方法一的一样

 

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

?
1
2
3
4
5
6
7
8
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
)

结果

?
1
2
3
4
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;
        }
    }  

}
View Code
复制代码

 

　

　　

　　

　　

　

posted @   海之殇  阅读(4100)  评论(0)  编辑  收藏  举报
编辑推荐：
· SQL Server 2025 AI相关能力初探
· Linux系列：如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
阅读排行：
· 阿里最新开源QwQ-32B，效果媲美deepseek-r1满血版，部署成本又又又降低了！
· Manus重磅发布：全球首款通用AI代理技术深度解析与实战指南
· 开源Multi-agent AI智能体框架aevatar.ai，欢迎大家贡献代码
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· AI技术革命，工作效率10个最佳AI工具
点击右上角即可分享
微信分享提示
AI IDE Trae