Introduction
Quick Start
Table Design
Data Loading
Data Export
Using StarRocks
Reference
- SQL Reference
  - User Account Management
    - ALTER USER
    - CREATE ROLE
    - CREATE USER
    - DROP ROLE
    - DROP USER
    - GRANT
    - REVOKE
    - SET PASSWORD
    - SET PROPERTY
    - SHOW GRANTS
  - Cluster Management
  - DDL
  - DML
  - Data Types
    - Numeric
      - BIGINT
      - LARGEINT
      - SMALLINT
      - TINYINT
      - BOOLEAN
      - DECIMAL
      - DOUBLE
      - FLOAT
      - INT
    - String
      - CHAR
      - VARCHAR
      - STRING
    - Date
      - DATE
      - DATETIME
    - Others
      - HLL
      - BITMAP
      - JSON
  - Auxiliary Commands
    - DESCRIBE
- Function Reference
  - Date Functions
    - convert_tz
    - curdate
    - current_timestamp
    - curtime
    - datediff
    - date_add
    - date_format
    - date_sub
    - date_trunc
    - day
    - dayname
    - dayofmonth
    - dayofweek
    - dayofyear
    - from_days
    - from_unixtime
    - hour
    - minute
    - month
    - monthname
    - now
    - second
    - str_to_date
    - timediff
    - timestampadd
    - timestampdiff
    - to_date
    - to_days
    - unix_timestamp
    - utc_timestamp
    - weekofyear
    - year
  - Geographic Functions
  - String Functions
    - append_trailing_char_if_absent
    - ascii
    - char_length
    - concat
    - concat_ws
    - ends_with
    - find_in_set
    - get_json_double
    - get_json_int
    - get_json_string
    - group_concat
    - instr
    - lcase
    - left
    - length
    - locate
    - lower
    - lpad
    - ltrim
    - money_format
    - null_or_empty
    - regexp_extract
    - regexp_replace
    - repeat
    - reverse
    - right
    - rpad
    - split
    - split_part
    - starts_with
    - strleft
    - strright
  - JSON Functions
    - Overview of JSON functions and operators
    - JSON constructor functions
    - JSON query and processing functions
    - JSON operators
  - Aggregate Functions
    - any_value
    - approx_count_distinct
    - avg
    - bitmap
    - count
    - hll_union_agg
    - max
    - min
    - percentile_approx
    - retention
    - stddev
    - stddev_samp
    - sum
    - variance
    - square
    - var_samp
  - Bitmap Functions
  - Array Functions
  - cast function
  - hash function
  - Cryptographic Functions
    - md5
    - sha2
  - Math Functions
    - RAND
    - square
- System Variables
- Error Code
- System Limit
Administration
- Build in Docker
- Manage a cluster
- Configuration
- Scale In and Out
- Backup and Restore
- Data Recovery
  - Data Recovery
  - Restore FEs
- Replica
- Query Planning
- Profiling
- Monitor and Alert
- User Privilege
- Authentication
- Resource Management
FAQ
- Deploy
- Data Migration
  - Data Ingestion
  - Data Export
- SQL
- Other FAQs
Benchmark
- SSB Benchmark
- TPC-H Benchmark
Release Notes
- v2.2
- v2.1
- v2.0
- v1.19

array_intersect

Description

Returns an array of the elements in the intersection of one or more arrays.

Syntax

array_intersect(input0, input1, ...)

Parameters

input: one or more arrays whose intersection you want to obtain. Specify arrays in the (input0, input1, ...) format and make sure that the arrays that you specify are of the same data type.

Return value

Returns an array of the same data type as the arrays that you specify.

Examples

Example 1:

mysql> SELECT array_intersect(["SQL", "storage"], ["mysql", "query", "SQL"], ["SQL"])
AS no_intersect ;
+--------------+
| no_intersect |
+--------------+
| ["SQL"]      |
+--------------+

Example 2:

mysql> SELECT array_intersect(["SQL", "storage"], ["mysql", null], [null]) AS no_intersect ;
+--------------+
| no_intersect |
+--------------+
| []           |
+--------------+

Example 3:

mysql> SELECT array_intersect(["SQL", null, "storage"], ["mysql", null], [null]) AS no_intersect ;
+--------------+
| no_intersect |
+--------------+
| [null]       |
+--------------+

In this article

array_intersect