logo_1Altinity Knowledge Base
    • Altinity Knowledge Base
      • Engines
        • Atomic Database Engine
          • How to Convert Ordinary to Atomic
          • How to Convert Atomic to Ordinary
        • EmbeddedRocksDB & dictionary
        • MergeTree table engine family
          • CollapsingMergeTree vs ReplacingMergeTree
          • Part names & MVCC
          • Proper ordering and partitioning the MergeTree tables
          • AggregatingMergeTree
          • index & column files
          • Merge performance and OPTIMIZE FINAL
          • Nulls in order by
          • ReplacingMergeTree
            • ReplacingMergeTree does not collapse duplicates
          • Skip index
          • SummingMergeTree
      • Queries & Syntax
        • GROUP BY
          • GROUP BY tricks
        • DateTime64
        • DISTINCT & GROUP BY & LIMIT 1 BY what the difference
        • Imprecise literal Decimal or Float64 values
        • Multiple aligned date columns in PARTITION BY expression
        • Slow `SELECT count()`
        • trace_log
        • Using array functions to mimic window-functions alike behavior
        • -State & -Merge combinators
        • ALTER MODIFY COLUMN is stuck, the column is inaccessible.
        • ANSI SQL mode
        • Async INSERTs
        • Atomic insert
        • Cumulative Anything
        • Data types on disk and in RAM
        • DELETE via tombstone column
        • EXPLAIN query
        • Fill missing values at query time
        • FINAL clause speed
        • Join with Calendar using Arrays
        • JOINs
          • JOIN table engine
        • JSONExtract to parse many attributes at a time
        • KILL QUERY
        • Lag / Lead
        • Machine learning in ClickHouse
        • Mutations
        • OPTIMIZE vs OPTIMIZE FINAL
        • Parameterized views
        • partial-projection-optimization
        • PIVOT / UNPIVOT
        • Possible deadlock avoided. Client should retry
        • Projections examples
        • Roaring bitmaps for calculating retention
        • SAMPLE by
        • Sampling Example
        • Simple aggregate functions & combinators
        • Skip indexes
          • Example: minmax
          • Skip index bloom_filter Example
          • Skip indexes examples
        • Time zones
        • Time-series alignment with interpolation
        • Top N & Remain
        • Troubleshooting
        • TTL
          • MODIFY (ADD) TTL
          • What are my TTL settings
          • TTL GROUP BY Examples
          • TTL Recompress example
        • UPDATE via Dictionary
        • Values mapping
        • Window functions
      • Functions
        • -Resample vs -If vs -Map vs Subquery
        • arrayMap, arrayJoin or ARRAY JOIN memory usage
        • assumeNotNull and friends
        • Encrypt
        • sequenceMatch
      • Integrations
        • Cloud Services
          • Altinity Cloud Access Management
        • Integration Clickhouse with MySQL
        • ODBC Driver for ClickHouse
        • Spark
        • BI Tools
        • CatBoost / MindsDB / Fast.ai
        • Google S3 (GCS)
        • Kafka
          • Adjusting librdkafka settings
          • Error handling
          • Exactly once semantics
          • Kafka main parsing loop
          • Kafka parallel consuming
          • Rewind / fast-forward / replay
          • SELECTs from engine=Kafka
      • Setup & maintenance
        • S3 & object storage
          • AWS S3 Recipes
          • S3Disk
        • AggregateFunction(uniq, UUID) doubled after ClickHouse upgrade
        • Can not connect to my ClickHouse server
        • cgroups and k8s
        • ClickHouse and different filesystems
        • Clickhouse logs and Vector.dev
        • ClickHouse operator
        • clickhouse-operator metrics names from clickhouse-server
        • Convert uniqExact to uniq(Combined)
        • Custom Settings
        • Description of asynchronous_metrics
        • disk encryption
        • How ALTER's works in ClickHouse
        • http_handlers
        • Logging
        • Precreate parts using clickhouse-local
        • RBAC example
        • recovery-after-complete-data-loss
        • Replication: Can not resolve host of another clickhouse server
        • source parts sizeis greater than the current maximum
        • Successful ClickHouse deployment plan
        • sysall database
        • Timeouts during OPTIMIZE FINAL
        • Useful settings to turn on
        • Who ate my CPU
        • ZooKeeper session has expired
        • Altinity packaging compatibility >21.x and earlier
        • AWS EC2 Storage
        • ClickHouse in Docker
        • ClickHouse Monitoring
        • ClickHouse versions
        • clickhouse-backup
        • Converting MergeTree to Replicated
        • Data Migration
          • Export from MSSQL to ClickHouse
          • clickhouse-copier
            • clickhouse-copier 20.3 and earlier
            • clickhouse-copier 20.4 - 21.6
            • Kubernetes job for clickhouse-copier
          • Distributed table to cluster
          • Fetch Alter Table
          • Remote table function
          • rsync
        • DDLWorker
          • There are N unfinished hosts (0 of them are currently active).
        • differential backups using clickhouse-backup
        • High CPU usage
        • Load balancers
        • memory configuration settings
        • Moving a table to another device.
        • Object consistency in a cluster
        • Production Cluster Configuration Guide
          • Backups
          • Cluster Configuration FAQ
          • Cluster Configuration Process
          • Hardware Requirements
          • Network Configuration
          • Version Upgrades
        • Replication queue
        • Schema migration tools for ClickHouse
          • golang-migrate
        • Server config files
        • Settings to adjust
        • Shutting down a node
        • SSL connection unexpectedly closed
        • Suspiciously many broken parts
        • System tables ate my disk
      • Useful queries
        • Check table metadata in zookeeper
        • Debug hunging thing
        • Handy queries for a system.query_log
        • Ingestion metrics from system.part_log
        • Can detached parts be dropped?
        • Database Size - Table - Column size
        • Datasets
        • Number of active parts in a partition
        • Parts consistency
      • Schema design
        • ClickHouse row-level deduplication
        • Column backfilling from dictionary
        • Functions to count uniqs
        • How much is too much?
        • How to change ORDER BY
        • insert deduplication
        • Pre-Aggregation approaches
        • Best schema for storing many metrics registered from the single source
        • Codecs
          • Codecs on array columns
          • Codecs speed
          • How to test different compression codecs
        • Dictionaries vs LowCardinality
        • Flattened table
        • Floats vs Decimals
        • Ingestion performance and formats
        • IPs/masks
        • JSONAsString and Mat. View as JSON parser
        • LowCardinality
        • MATERIALIZED VIEWS
          • Idempotent insert MV
          • Backfill/populate MV in a controlled manner
      • Kubernetes
        • Possible issues with running ClickHouse in k8s
      • Interfaces
        • clickhouse-client
      • Upgrade
        • Vulnerabilities
        • Clickhouse Function/Engines/Settings Report
        • Removing empty parts
        • Removing lost parts
      • Dictionaries
        • Dictionaries & arrays
        • Dictionary on the top of several tables using VIEW
        • Example of PostgreSQL dictionary
        • MySQL8 source for dictionaries
        • Partial updates
        • range_hashed example - open intervals
        • Security named collections
        • SPARSE_HASHED VS HASHED vs HASHED_ARRAY
      • Using This Knowledge Base
        • Mermaid Example

    Edit this page Create child page Create documentation issue Print entire section

    Share on Social Media:

    1. Queries & Syntax
    2. JOINs

    JOINs

    See presentation:

    https://github.com/ClickHouse/clickhouse-presentations/blob/master/meetup38/join.pdf

    https://excalidraw.com/#json=xX_heZcCu0whsDmC2Mdvo,ppbUVFpPz-flJu5ZDnwIPw


    JOIN table engine

    Last modified 2022.07.29: Update _index.md (8564f77)
    © 2023 Altinity Inc. Altinity® and Altinity.Cloud® are registered trademarks of Altinity, Inc. ClickHouse® is a registered trademark of ClickHouse, Inc. All Rights Reserved Privacy Policy